Oracle中in与exist,notin与notexist的性能问题.docxVIP

上星期五与haier讨论in跟exists的性能问题，正好想起原来公司的一个关于not in的规定，本想做个实验证明我的观点是正确的，但多次实验结果却给了我一个比较大的教训。我又咨询了下oracle公司工作的朋友，确实是我持有的观点太保守了。于是写个文章总结下，希望对大家有所启发。后面可能有大篇是关于10053 trace的内容，只作实验证明，可直接忽略看最终的结论即可。 我们知道，in?是把外表和内表作hash?连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的说法是不准确的。?如果查询的两个表大小相当，那么用in和exists是差别不大的。但如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in，效率才是最高的。 假定表A（小表），表B（大表），cc列上都有索引：? select?*?from?A?where?cc?in?(select?cc?from?B); --效率低，用到了A表上cc列的索引 select?*?from?A?where?exists(select?cc?from?B?where?cc=A.cc); --效率高，用到了B表上cc列的索引。? 相反的： select?*?from?B?where?cc?in?(select?cc?from?A); --效率高，用到了B表上cc列的索引 select?*?from?B?where?exists(select?cc?from?A?where?cc=B.cc); --效率低，用到了A表上cc列的索引 通过使用exists，Oracle会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。在执行子查询之前， HYPERLINK /os/ \t _blank 系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。 那not in跟exists呢？如果查询语句使用了not?in?那么内外表都进行全表扫描，没有用到索引；而not?exists?的子查询依然能用到表上的索引。所以无论那个表大，用not?exists都比not?in要快。? not?in?逻辑上不完全等同于not?exists，?请看下面的例子：? create?table?t1?(c1?number,c2?number);? create?table?t2?(c1?number,c2?number);?? insert?into?t1?values?(1,2);? insert?into?t1?values?(1,3);? insert?into?t2?values?(1,2);? insert?into?t2?values?(1,null);?? select?*?from?t1?where?c2?not?in?(select?c2?from?t2);?--结果是no?rows?found? select?*?from?t1?where?not?exists?(select?1?from?t2?where?t1.c2=t2.c2);?--结果是1?3 正如所看到的，not?in?出现了不期望的结果集，存在逻辑错误。如果看一下上述两个select语句的执行计划，也会不同。后者使用了hash join。因此，请尽量不要使用not?in(它会调用子查询)，而尽量使用not?exists(它会调用关联子查询)。如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录，正如上面例子所示。除非子查询字段有非空限制，这时可以使用not?in?,并且也可以通过提示让它使用hasg_aj或merge_aj连接。not in (...) 括号中的返回值不能存在null值，是Oracle SQL开发的一条铁律。 我们再看下性能方面。关于这2个谁的性能好坏的讨论从来就没有停止过，我不想牵扯进去。。。只是先提出一条，基于哪个oracle的版本。为什么？因为oracle的CBO算法是一直在优化当中的。这时，你应该心存感谢，因为我们写的非常多的性能不高的sql，oracle都默默地绞尽脑汁地给你优化过了。。。废话不多说，我们建2个表用来实验下： create table test1 (col number); create table test2 (col number); 然后插入一些数据： insert into test1 select level from dual connect by level =100000; insert i