oracle exists&not exists用法及与in&not in比较(10g)-开发系列（四）

注：以下内容适合 初学oracle开发或者java等开发者，高手略过

一 exists&in

以下三个语句  功能都是从 iodso.qos_hisentry_sheet_jtext_td 里面找到  sheet_no在  iodso.qos_hisentry_sheet_td表 arch_time 1天时间里面的单子。

iodso.qos_hisentry_sheet_jtext_td 有个普通的联合索引                  

iodso.qos_hisentry_sheet_td  有个普通的索引                                  

两个表的数据量情况

select count(1) from iodso.qos_hisentry_sheet_td-- 29843027

select count(1) from  iodso.qos_hisentry_sheet_jtext_td--29973242

1

select *
  from iodso.qos_hisentry_sheet_jtext_td t
 where t.sheet_no in (select a.sheet_no
                        from iodso.qos_hisentry_sheet_td a
                       where a.arch_time between trunc(sysdate - 1, 'dd') and
                             trunc(sysdate, 'dd'));

2

select *
  from iodso.qos_hisentry_sheet_jtext_td t
 where t.sheet_no in (select a.sheet_no
                        from iodso.qos_hisentry_sheet_td a
                       where a.arch_time between trunc(sysdate - 1, 'dd') and
                             trunc(sysdate, 'dd')
                         and t.sheet_no = a.sheet_no);

3

select *
  from iodso.qos_hisentry_sheet_jtext_td t
 where exists (select a.sheet_no
          from iodso.qos_hisentry_sheet_td a
         where a.arch_time between trunc(sysdate - 1, 'dd') and
               trunc(sysdate, 'dd')
           and t.sheet_no = a.sheet_no);

执行计划比较

执行计划由pl/sql Dev的F5键生成  ，一般看执行计划会建议从sqlplus explain plan for看 但是开发者可能更习惯用pl、sql工具

且工具能定位到第一个执行的地方  且对应的操作描述 在最下方有一串英文 如下图 sort_unique 的解释 在最下面红圈的地方 sort a result set and eliminate duplicates 意思是对结果集排序并且去重

sql 1 的计划：

sql 3 的计划：

sql 2的计划：

从上面的执行计划及顺序来看 三个sql 完全一样。

执行结果

sql 1的执行结果：

sql2 的执行结果：

sql3 的执行结果：

从以上来看 是sql1 执行的最快 sql2 执行的最慢

上面是从查小表的情况 再看看下面语句的情况（查大表的情况）：

1 

select a.*
  from iodso.qos_hisentry_sheet_td a
 where a.arch_time between trunc(sysdate - 1, 'dd') and
       trunc(sysdate, 'dd')
   and sheet_no in
       (select sheet_no from iodso.qos_hisentry_sheet_jtext_td t);

2

select a.*
          from (select *
            from iodso.qos_hisentry_sheet_td a
           where a.arch_time between trunc(sysdate - 1, 'dd') and
                 trunc(sysdate, 'dd')) a
         where exists (select t.sheet_no
                  from iodso.qos_hisentry_sheet_jtext_td t
                 where t.sheet_no = a.sheet_no);

所以 网上很多说的 exists 比 in快 或者 检索大表的时候 exists比 in快 等等 不一定都是准确的，现在百度的很多东西可能都是复制来复制去，还有的是以前8i 9i老版本的规则 现在基本都是10g以上 不一定适用。网上的结论要慎用 最好自己试验下。

exists 和 in的效率通常情况是差不多的，需要看执行计划及实际上执行时间为准，。

ps:大部分的企业级开发者可能更喜欢用in 易于平常的思维理解

二 not exists&not in

1

select t.occur_area_id-1,
  COUNT(1) ALL_NUM,
   SUM(CASE
             WHEN (DECODE(SIGN(T.FLOW_TIME - t.fact_flow_time), -1, 0, 1) = 0) THEN
              1
             ELSE
              0
           END) CS_NUM
  from  QOS_NET_CONTROL_GD_sb T
where t.sheet_no not in
(SELECT  t1.sheet_no
  FROM QOS_NET_CONTROL_GD_sb T1,
IODSO.QOS_EOSORG_T_EMPLOYEE     T2,
          IODSO.QOS_EOSORG_T_ORGANIZATION T3,
       iodso.qos_eosoperator t6
 WHERE T1.USERID = T6.userid
   and t6.operatorid = t2.operatorid
   and t2.orgid=t3.orgid
 and T1.STAT_DATE = TO_DATE('2014-11-08', 'YYYY-MM-DD')
   AND T1.STAT_DATE = TO_DATE('2014-11-08', 'YYYY-MM-DD')  
)
group by t.occur_area_id;

2

select t.occur_area_id - 1,  
       COUNT(1) ALL_NUM,  
       SUM(CASE  
             WHEN (DECODE(SIGN(T.FLOW_TIME - t.fact_flow_time), -1, 0, 1) = 0) THEN  
              1  
             ELSE  
              0  
           END) CS_NUM  
  from QOS_NET_CONTROL_GD_sb T  
 where not exists  
 (select 1  
          from QOS_NET_CONTROL_GD_sb           s,  
               IODSO.QOS_EOSORG_T_EMPLOYEE     T2,  
               IODSO.QOS_EOSORG_T_ORGANIZATION T3,  
               iodso.qos_eosoperator           t6  
         where T.Sheet_No = s.sheet_no  
           and s.USERID = T6.userid  
           and t6.operatorid = t2.operatorid  
           and t2.orgid = t3.orgid)  
           and T.STAT_DATE = TO_DATE('2014-11-08', 'YYYY-MM-DD')  
 group by t.occur_area_id

从上面执行计划可以看到 cost 差别很大 ，not exists 比not in 的小很多。 not exists使用的是hash join anti 而 not in 使用的是filter。执行时间来看 not exists 几分钟 not in 执行了30分钟还没完成。

小总结：(此内容转)
Semi-join
通常出现在使用了exists或in的sql中，所谓semi-join即在两表关联时，当第二个表中存在一个或多个匹配记录时，返回第一个表的记录；
与普通join的区别在于semi-join时，第一个表里的记录最多只返回一次

Anti-join
第二张表没有发现匹配记录时，才会返回第一张表里的记录；
何时选择anti-join1
使用not in且相应列有not null约束
not exists，不保证每次都用到anti-join
当无法选择anti-join时，oracle常会采用filter替代

filter
是对外表的每一行，都要对内表执行一次全表扫描，他其实很像我们熟悉的neested loop，但它的独特之处在于会维护一个hash table

三 两个表根据某字段关联更新

update ap
   set ap.t =
       (select bp.t from bp where ap.s = bp.s)
 where exists (select 1 from bp where ap.s = bp.s);
commit;

语句看似很简单 但是当ap  bp本身都是很复杂的查询的时候 可能想到这个比较困难了。