时间戳

首页 > 解决方案 > Oracle - 当我使用 EXISTS 时，关系运算符无效

plsql - Oracle - 当我使用 EXISTS 时，关系运算符无效

问题描述

我收到了以下查询，当满足条件时，从表 et_fact_reclam_ter_his_misc 中删除返回的记录：

DELETE FROM et_fact_reclam_ter_his_misc
 WHERE num_siniest || num_exped IN
 (SELECT DISTINCT num_siniest || num_exped
          FROM et_fact_reclam_ter_his_misc t1
         WHERE NOT EXISTS (SELECT *
                  FROM et_fact_reclam_ter_misc t2
                 WHERE t1.num_siniest = t2.num_siniest));

我一直在搜索，发现使用EXISTS而不是IN会提高查询性能。但是我遇到了2个错误。第一个是当我将IN运算符替换为时出现的错误消息EXISTS：

DELETE FROM et_fact_reclam_ter_his_misc
 WHERE num_siniest || num_exped EXISTS
 (SELECT DISTINCT num_siniest || num_exped
          FROM et_fact_reclam_ter_his_misc t1
         WHERE NOT EXISTS (SELECT *
                  FROM et_fact_reclam_ter_misc t2
                 WHERE t1.num_siniest = t2.num_siniest));


ORA-00920: invalid relational operator

第二个是当我尝试将 a 嵌套SELECT在 中时DELETE，但返回的行数不同：

DELETE FROM et_fact_reclam_ter_his_misc
 WHERE EXISTS
 (SELECT FROM et_fact_reclam_ter_his_misc
         WHERE num_siniest || num_exped IN
               (SELECT DISTINCT num_siniest || num_exped
                  FROM et_fact_reclam_ter_his_misc t1
                 WHERE NOT EXISTS
                 (SELECT *
                          FROM et_fact_reclam_ter_misc t2
                         WHERE t1.num_siniest = t2.num_siniest)));

感谢您对提高查询性能的任何帮助

标签： plsqloracle11gexists

解决方案

你可以试试下面，不是连接字段的最佳方法，

DELETE FROM et_fact_reclam_ter_his_misc A
 WHERE EXISTS
 (  SELECT 1 FROM 
         (SELECT DISTINCT num_siniest || num_exped
          FROM et_fact_reclam_ter_his_misc t1) B
          WHERE A.num_siniest|| A.num_exped = B.num_siniest || B.num_exped 
         AND NOT EXISTS (SELECT 1
                  FROM et_fact_reclam_ter_misc t2
                 WHERE B.num_siniest = t2.num_siniest));

似乎连接的列是数字，它会迫使 oracle 将其强制转换为 char，这可能会影响性能。如果您要处理大量记录，则低于一个应该会更快。

DELETE FROM et_fact_reclam_ter_his_misc A
 WHERE EXISTS
 (  SELECT 1 FROM 
         (SELECT DISTINCT num_siniest, num_exped
          FROM et_fact_reclam_ter_his_misc t1) B
          WHERE A.num_siniest = B.num_siniest
          AND  A.num_exped = B.num_exped 
         AND NOT EXISTS (SELECT 1
                  FROM et_fact_reclam_ter_misc t2
                 WHERE B.num_siniest = t2.num_siniest));

DISTINCT 会矫枉过正，用 group by 代替肯定会好得多，所以最好的一个是：

DELETE FROM et_fact_reclam_ter_his_misc A
 WHERE EXISTS
 (  SELECT 1 FROM 
         (SELECT num_siniest, num_exped,count(1)
          FROM et_fact_reclam_ter_his_misc t1
          group by num_siniest, num_exped) B
          WHERE A.num_siniest = B.num_siniest
          AND  A.num_exped = B.num_exped 
         AND NOT EXISTS (SELECT 1
                  FROM et_fact_reclam_ter_misc t2
                 WHERE B.num_siniest = t2.num_siniest));

推荐阅读