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

注：以下内容适合 初学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¬ 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