in/exists和not in/not exists执行效率

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:in/exists和not in/not exists执行效率

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

一、IN 与EXISTS
1、理解
IN的执行流程
SELECT * FROM T1 WHERE X IN (SELECT Y FROM T2)
事实上可以理解为:
SELECT * FROM T1, (SELECT DISTINCT Y FROM T2) T2 WHERE T1.X = T2.Y
从这里可以看出,IN需要先处理T2表,然后再和T1进行关联

EXISTS的执行流程

SELECT * FROM T1 WHERE EXISTS (SELECT NULL FROM T2 WHERE Y = X)
--可以理解为:
for x in ( select * from t1 ) LOOP
    if ( exists ( select null from t2 where y = x.x )THEN 
        OUTPUT THE RECORD
    end if
end loop

从这里看出,EXISXTS会先查询T1表,然后再LOOP处理T2表

2、结论
对于in 和 exists的区别: 如果子查询得出的结果集记录较少,主查询中的表较大且又有索引时应该用in, 反之如果外层的主查询记录较少,子查询中的表大,又有索引时使用exists。其实我们区分in和exists主要是造成了驱动顺序的改变(这是性能变化的关键),如果是exists,那么以外层表为驱动表,先被访问,如果是IN,那么先执行子查询,所以我们会以驱动表的快速返回为目标,那么就会考虑到索 引及结果集的关系了。
综合以上对IN/EXISTS的讨论,我们可以得出一个基本通用的结论:IN适合于外表大而内表小的情况;EXISTS适合于外表小而内表大的情况。

二、NOT IN 与NOT EXISTS
1、理解
NOT IN的执行流程
SELECT * FROM T1 WHERE X NOT IN (SELECT Y FROM T2)
事实上可以理解为:
SELECT * FROM T1, (SELECT DISTINCT Y FROM T2) T2 WHERE T1.X != T2.Y

NOT EXISTS的执行流程

SELECT .. .. .
  FROM ROLLUP R
 WHERE NOT EXISTS
 (SELECT 'Found' FROM TITLE T WHERE R.SOURCE_ID = T.TITLE_ID);
--可以理解为:
for x in ( select * from rollup )
       loop
           if ( not exists ( that query ) ) then
                  OUTPUT
           end if;
        end;

注意:NOT EXISTS 与 NOT IN 不能完全互相替换,看具体的需求。如果选择的列可以为空,则不能被替换。具体见:in/exists和not in/not exists语意探讨

2、结论
not in 只有当子查询中,select 关键字后的字段有not null约束或者有这种暗示时用not in,另外如果主查询中表大,子查询中的表小但是记录多,则应当使用not in,并使用anti hash join.如果主查询表中记录少,子查询表中记录多,并有索引,可以使用not exists,另外not in最好也可以用/*+ HASH_AJ */或者外连接+is null.一般情况下建议使用not exists

--比如:
SELECT .. .. ..
  FROM ROLLUP R
 WHERE NOT EXISTS
 (SELECT 'Found' FROM TITLE T WHERE R.SOURCE_ID = T.TITLE_ID);
--改成
SELECT .. .. ..
  FROM TITLE T, ROLLUP R
 WHERE R.SOURCE_ID = T.TITLE_ID(+)
   AND T.TITLE_ID IS NULL;
--或者
SELECT /*+ HASH_AJ */
.. .. ..
  FROM ROLLUP R
 WHERE OURCE_ID NOT IN
       (SELECT OURCE_ID FROM TITLE T WHERE OURCE_ID IS NOT NULL);
此条目发表在 Oracle 开发 分类目录。将固定链接加入收藏夹。

in/exists和not in/not exists执行效率》有 2 条评论

  1. 鲜花礼品 说:

    只要过程和结果所耗时间能让各方接受,用哪个都可以

  2. select.简单 说:

    总结的很好哈,不过感觉有点太果断了,in和exist会互相转换的,个人理解in和exist这块还要看是否进行unnest,然后进行unnest之后连接方式是否正确