Oracle中in与existnot in与not exist的性能问题.docx

1、Oracle中in与existnot in与not exist的性能问题上星期五与haier讨论in跟exists的性能问题，正好想起原来公司的一个关于not in的规定，本想做个实验证明我的观点是正确的，但多次实验结果却给了我一个比较大的教训。我又咨询了下oracle公司工作的朋友，确实是我持有的观点太保守了。于是写个文章总结下，希望对大家有所启发。后面可能有大篇是关于10053 trace的内容，只作实验证明，可直接忽略看最终的结论即可。我们知道，in是把外表和内表作hash连接，而exists是对外表作loop循环，每次loop循环再对内表进行查询。一直以来认为exists比in效率高的

2、说法是不准确的。如果查询的两个表大小相当，那么用in和exists是差别不大的。但如果两个表中一个较小，一个是大表，则子查询表大的用exists，子查询表小的用in，效率才是最高的。假定表A（小表），表B（大表），cc列上都有索引： select*fromAwhereccin(selectccfromB); -效率低，用到了A表上cc列的索引 select*fromAwhereexists(selectccfromBwherecc=A.cc); -效率高，用到了B表上cc列的索引。相反的： select*fromBwhereccin(selectccfromA); -效率高，用到了B表上cc列

3、的索引 select*fromBwhereexists(selectccfromAwherecc=B.cc); -效率低，用到了A表上cc列的索引通过使用exists，Oracle会首先检查主查询，然后运行子查询直到它找到第一个匹配项，这就节省了时间。Oracle在执行IN子查询时，首先执行子查询，并将获得的结果列表存放在一个加了索引的临时表中。在执行子查询之前，系统先将主查询挂起，待子查询执行完毕，存放在临时表中以后再执行主查询。这也就是使用EXISTS比使用IN通常查询速度快的原因。那not in跟exists呢？如果查询语句使用了notin那么内外表都进行全表扫描，没有用到索引；而not

4、exists的子查询依然能用到表上的索引。所以无论那个表大，用notexists都比notin要快。notin逻辑上不完全等同于notexists，请看下面的例子：createtablet1(c1number,c2number);createtablet2(c1number,c2number);insertintot1values(1,2);insertintot1values(1,3);insertintot2values(1,2);insertintot2values(1,null);select*fromt1wherec2notin(selectc2fromt2);-结果是norowsf

5、oundselect*fromt1wherenotexists(select1fromt2wheret1.c2=t2.c2);-结果是13正如所看到的，notin出现了不期望的结果集，存在逻辑错误。如果看一下上述两个select语句的执行计划，也会不同。后者使用了hash join。因此，请尽量不要使用notin(它会调用子查询)，而尽量使用notexists(它会调用关联子查询)。如果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录，正如上面例子所示。除非子查询字段有非空限制，这时可以使用notin,并且也可以通过提示让它使用hasg_aj或merge_aj连接。not in (.

6、) 括号中的返回值不能存在null值，是Oracle SQL开发的一条铁律。我们再看下性能方面。关于这2个谁的性能好坏的讨论从来就没有停止过，我不想牵扯进去。只是先提出一条，基于哪个oracle的版本。为什么？因为oracle的CBO算法是一直在优化当中的。这时，你应该心存感谢，因为我们写的非常多的性能不高的sql，oracle都默默地绞尽脑汁地给你优化过了。废话不多说，我们建2个表用来实验下：create table test1 (col number);create table test2 (col number);然后插入一些数据：insert into test1select leve

7、l from dual connect by level =100000;insert into test2select level+1 from dual connect by level select * from v$version where rownum=1;BANNER-Oracle Database 10g Release 10.1.0.5.0 Production17:17:01 SQL set timing on17:17:47 SQL select * from test1 where not exists (select 1 from test2 where test1.

8、col = test2.col); COL- 1Elapsed: 00:00:00.2517:17:59 SQL select * from test1 where col not in (select col from test2); COL- 1Elapsed: 00:08:31.14确实，两者所需要的时间非常大，这也是我们最常看到的结果。看下执行计划：我们发现，对于oracle 10g，第一个sql没什么可说的了，出现了hash join，直接跳过。对于第2个sql，可以看到，关联谓词是filter，它类似于两表关联中的nested loop，也就是跑两层循环，可见它的效率有多差。为什么

9、not in不能使用hash join作为执行计划呢？正如上面解释的，因为内表或外表中存在空值对最终结果产生的影响是hash join无法实现的，因为hash join不支持把空值放到hash桶中，所以它没办法处理外表和内表中存在的空值，效率与正确性放在一起时，肯定是要选择正确性，所以oracle必须放弃效率，保证正确性，采用filter谓词。这个执行计划中我们还有感兴趣的东西，那就是：LNNVL(COL:B1)，它在这里的作用很巧妙，oracle知道使用filter性能很差，所以它在扫描内表test2时，会使用LNNVL来检查test2.col是否存在null值，只要扫描到null值，就可以

10、断定最终的结果为空值，也就没有了继续执行的意义，所以oracle可以马上终止执行，在某种意义上它弥补了filter较差的性能。而具体的优化方法，我们通过最后一个实验来解决。我们再换一个11g的库看一下：17:16:17 SQL select * from v$version where rownum=1;BANNER-Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production17:15:44 SQL set timing on17:15:48 SQL select * from test1 where

11、 not exists (select 1 from test2 where test1.col = test2.col); COL- 1Elapsed: 00:00:00.2417:16:08 SQL select * from test1 where col not in (select col from test2); COL- 1Elapsed: 00:00:00.06看下它的执行计划：当时看到这执行计划时，确实让我有点不解了，2者的执行计划几乎是一样的，只是在谓语分析部分跟hash join的方式略有不同。再试一次，结果也是一样的。我们也注意到，执行计划并没有使用到oracle的收集

12、信息，而是使用了动态采样。我们再尝试使用统计信息收集，再看下执行计划，结果是一样的。当然，因为使用了hash join，结果基本是瞬间的事情。于是我又咨询了下oracle公司工作的朋友，其实，in和exist的区别只在10.2.0.3及以前的版本中存在；而10.2.0.4及以后的版本中，in和exist的效果是完全一样的。以下是Oracle官方的解释：In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subqu

13、ery, then use IN. If the selective predicate is in the parent query, then use EXISTS.Sometimes, Oracle can rewrite a subquery when used with an IN clause to take advantage of selectivity specified in the subquery. This is most beneficial when the most selective filter appears in the subquery and the

14、re are indexes on the join columns. Conversely, using EXISTS is beneficial when the most selective filter is in the parent query. This allows the selective predicates in the parent query to be applied before filtering the rows against the EXISTS criteria.所以以后大家遇到有类型的情况，优化使用not exist，毕竟是在所有oracle版本中通

15、用的。我们再来看一个对not in优化的思路：首先来看两个sql，返回结果相同，但是耗时差别很大：SQL select * from v$version where rownum=1;BANNER-Oracle Database 10g Release 10.2.0.1.0 ProductionSQL select count(*) 2 from justin_good r 3 where not exists 4 (select x from justin_count pc where pc.id = r.justin_good_id) 5 ; COUNT(*)- 7229Executed

16、in 3.437 secondsSQL select count(*) 2 from justin_good r 3 where r.justin_good_id not in 4 (select pc.id from justin_count pc) 5 ; COUNT(*)- 7229Executed in 128.203 seconds再来看一下它们的执行计划使用not exist的语句cost为3452，而not in 的却达到14216SQL explain plan for select count(*) 2 from justin_good r 3 where not exist

17、s 4 (select x from justin_count pc where pc.id = r.justin_good_id);Explained.SQL select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT-Plan hash value: 1087925722-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 9 | 3452 (2)| 00:00:42 | 1 | SORT AGGREGA

18、TE | | 1 | 9 | | |* 2 | HASH JOIN RIGHT ANTI | | 59 | 531 | 3452 (2)| 00:00:42 | 3 | INDEX FAST FULL SCAN| PK11_1 | 4562 | 22810 | 4 (0)| 00:00:01 | 4 | TABLE ACCESS FULL | justin_good | 602K| 2355K| 3440 (2)| 00:00:42 |-PLAN_TABLE_OUTPUT-Predicate Information (identified by operation id):- 2 - acce

19、ss(PC.ID=R.justin_good_id)16 rows selected.SQL explain plan for select count(*) 2 from justin_good r 3 where r.justin_good_id not in 4 (select pc.id from justin_count pc);Explained.SQL select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT-Plan hash value: 4119029611-| Id | Operation | Name | Row

20、s | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STATEMENT | | 1 | 4 | 14216 (2)| 00:02:51 | 1 | SORT AGGREGATE | | 1 | 4 | | |* 2 | FILTER | | | | | | 3 | TABLE ACCESS FULL| justin_good | 602K| 2355K| 3442 (2)| 00:00:42 |* 4 | INDEX FULL SCAN | PK11_1 | 1 | 5 | 11 (0)| 00:00:01 |-PLAN_TABLE_OUTPUT-Pred

21、icate Information (identified by operation id):- 2 - filter( NOT EXISTS (SELECT 0 FROM justin.justin_count PC WHERE LNNVL(PC.ID:B1) 4 - filter(LNNVL(PC.ID:B1)18 rows selected.可以看到使用not exist的sql采用了hash join anti，而not in的却使用了filter改写一下语句,确保justin_good_id不会在查询中返回NULLSQL set linesize 300SQL explain pla

22、n for select count(*) 2 from justin_good r 3 where nvl(r.justin_good_id,NULL) not in 4 (select pc.id from justin_count pc);Explained.SQL select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT-Plan hash value: 1087925722-| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-| 0 | SELECT STA

23、TEMENT | | 1 | 9 | 3452 (2)| 00:00:42 | 1 | SORT AGGREGATE | | 1 | 9 | | |* 2 | HASH JOIN RIGHT ANTI | | 602K| 5298K| 3452 (2)| 00:00:42 | 3 | INDEX FAST FULL SCAN| PK11_1 | 4562 | 22810 | 4 (0)| 00:00:01 | 4 | TABLE ACCESS FULL | justin_good | 602K| 2355K| 3440 (2)| 00:00:42 |-PLAN_TABLE_OUTPUT-Pre

24、dicate Information (identified by operation id):-1 - access(PC.ID=NVL(R.justin_good_id,TO_NUMBER(NULL)SQL explain plan for select count(*) 2 from justin_good r 3 where r.justin_good_id not in 4 (select pc.id from justin_count pc) 5 and r.justin_good_id is not null;Explained.SQL select * from table(dbms_xplan.display);PLAN_TABLE_OUTPUT-