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

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

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

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

4、呢？如果查询语句使用了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 int

5、o t2 values (1,null);select * from t1 where c2 not in (select c2 from t2); -结果是no rows foundselect * 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(它会调用关联子查询)。如

6、果子查询中返回的任意一条记录含有空值，则查询将不返回任何记录，正如上面例子所示。除非子查询字段有非空限制，这时可以使用not in ,并且也可以通过提示让它使用hasg_aj或 merge_aj连接。 not in (.)括号中的返回值不能存在null值，是 Oracle SQL开发的一条铁律。我们再看下性能方面。关于这2 个谁的性能好坏的讨论从来就没有停止过，我不想牵扯进去。只是先提出一条，基于哪个oracle的版本。为什么？因为oracle的 CBO 算法是一直在优化当中的。这时，你应该心存感谢，因为我们写的非常多的性能不高的sql ， oracle都默默地绞尽脑汁地给你优化过了。废话不多

7、说，我们建2 个表用来实验下：create table test1 (col number);create table test2 (col number);然后插入一些数据：insert into test1select level 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 Release10.1.0.5.0Produ

8、ction17:17:01 SQL set timing on17:17:47 SQL select * from test1 where not exists (select 1 from test2 where test1.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确实，两者所需要的时间非常大，这也是我们最常看到的结果。看下执行计划：我们发现，对于 or

9、acle10g ，第一个 sql没什么可说的了，出现了hashjoin ，直接跳过。对于第2 个 sql ，可以看到，关联谓词是filter ，它类似于两表关联中的nested loop，也就是跑两层循环，可见它的效率有多差。为什么 not in 不能使用 hash join作为执行计划呢？正如上面解释的，因为内表或外表中存在空值对最终结果产生的影响是hash join 无法实现的，因为hash join不支持把空值放到 hash桶中，所以它没办法处理外表和内表中存在的空值，效率与正确性放在一起时，肯定是要选择正确性，所以oracle必须放弃效率，保证正确性，采用filter 谓词。这个执行计

10、划中我们还有感兴趣的东西，那就是：LNNVL(COL:B1)，它在这里的作用很巧妙，oracle 知道使用 filter性能很差，所以它在扫描内表test2 时，会使用 LNNVL来检查 test2.col是否存在 null 值，只要扫描到null 值，就可以断定最终的结果为空值，也就没有了继续执行的意义，所以oracle可以马上终止执行，在某种意义上它弥补了filter较差的性能。而具体的优化方法，我们通过最后一个实验来解决。我们再换一个11g 的库看一下：17:16:17 SQL select * from v$version where rownum=1;BANNER-Oracle Da

11、tabase 11g Enterprise Edition Release 11.2.0.1.0 17:15:44 SQL set timing on- 64bit Production17:15:48 SQL select * from test1 where 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-1Ela

12、psed: 00:00:00.06看下它的执行计划：当时看到这执行计划时，确实让我有点不解了，2 者的执行计划几乎是一样的，只是在谓语分析部分跟hash join的方式略有不同。再试一次，结果也是一样的。我们也注意到，执行计划并没有使用到oracle的收集信息，而是使用了动态采样。我们再尝试使用统计信息收集，再看下执行计划，结果是一样的。当然，因为使用了hash join，结果基本是瞬间的事情。于是我又咨询了下oracle公司工作的朋友，其实，in 和 exist的区别只在10.2.0.3及以前的版本中存在；而10.2.0.4及以后的版本中，in 和 exist 的效果是完全一样的。以下是Or

13、acle官方的解释：In certain circumstances, it is better to use IN rather than EXISTS. In general, if the selective predicate is in the subquery, 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 advan

14、tage of selectivity specified in the subquery. This is most beneficial when the most selective filter appears in the subquery and there 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

15、in the parent query to be applied before filtering the rows against the EXISTS criteria.所以以后大家遇到有类型的情况，优化使用not exist，毕竟是在所有oracle版本中通用的。我们再来看一个对 not in优化的思路：首先来看两个sql ，返回结果相同，但是耗时差别很大：SQL select * from v$version where rownum=1;BANNER-Oracle Database 10g Release 10.2.0.1.0ProductionSQL select count(*

16、)2 from justin_good r3 where not exists4 (select x from justin_count pc where pc.id = r.justin_good_id)5 ;COUNT(*)-7229Executed in 3.437 secondsSQL select count(*)2 from justin_good r3 where r.justin_good_id not in4 (select pc.id from justin_count pc)5 ;COUNT(*)-7229Executed in 128.203 seconds再来看一下它

17、们的执行计划使用 not exist的语句 cost 为 3452 ，而 not in的却达到 14216SQL explain plan for select count(*)2 from justin_good r3 where not exists4 (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| Op

18、eration| Name| Rows| Bytes | Cost (%CPU)| Time |-|0| SELECT STATEMENT|1 |9 | 3452(2)| 00:00:42 |1| SORT AGGREGATE|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:4

19、2 |-PLAN_TABLE_OUTPUT-Predicate Information (identified by operation id):-2 - access(PC.ID=R.justin_good_id)16 rows selected.SQL explain plan for select count(*)2 from justin_good r3 where r.justin_good_id not in4 (select pc.id from justin_count pc);Explained.SQL select * from table(dbms_xplan.displ

20、ay);PLAN_TABLE_OUTPUT-Plan hash value: 4119029611-| Id| Operation| Name| Rows| 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

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

22、explain plan for select count(*)，而 not inNULL的却使用了filter2 from justin_good r3 where nvl(r.justin_good_id,NULL) not in4 (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 (%C

23、PU)| Time |-|0| SELECT STATEMENT|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-Predicate Inf

24、ormation (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 r3 where r.justin_good_id not in4 (select pc.id from justin_count pc)5 and r.justin_good_id is not null;Explained.SQL select * from table(dbms_xplan.displ

25、ay);PLAN_TABLE_OUTPUT-Plan hash value: 1087925722-| Id| Operation| Name| Rows| Bytes | Cost (%CPU)| Time |-|0| SELECT STATEMENT|1 |9 | 3454(2)| 00:00:42 |1| SORT AGGREGATE|1 |9 |*2 |HASH JOIN RIGHT ANTI |59 | 531 | 3454(2)| 00:00:42 |3|INDEX FAST FULL SCAN| PK11_1|4562 | 22810 |4(0)| 00:00:01 |* 4|T

26、ABLE ACCESS FULL| justin_good|602K| 2355K| 3442(2)| 00:00:42 |-PLAN_TABLE_OUTPUT-Predicate Information (identified by operation id):-2 - access(R.justin_good_id=PC.ID)4 - filter(R.justin_good_id IS NOT NULL)17 rows selected.另外查看表结构，发现列justin_good_id没有 not null约束SQL desc justin_goodNameNull?Type- - -

27、IDNOT NULL NUMBER(18)justin_good_idNUMBER(18)尝试添加 not null约束SQL alter table justin_good modify justin_good_id not null;Table altered.再来查看一下 not in 语句的执行计划，发现可以成功使用hash join anti，与使用 not exist计划一样SQL explain plan for select count(*)2 from justin_good r3 where r.justin_good_id not in4 (select pc.id fr

28、om 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 STATEMENT|1 |9 | 3452(2)| 00:00:42 |1| SORT AGGREGATE|1 |9 |*2 |HASH JOIN RIGHT ANTI |59 | 531 | 3452(2)| 00:00:

29、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 - access(R.justin_good_id=PC.ID)16 rows selected.SQL 优化完成后，尝试调用 10053 事件追踪一下，看看 ORACLE 内部是怎么实现这一

30、机制的首先来看一下 not in 的语句SQL select spid,pid from v$process where addr=(select paddr from v$session where sid=(select sid from v$mystat where rownum =1);SPIDPID- -30736441SQL alter session set events 10053 trace name context forever, level 12;Session alteredSQLSQL select count(*)2 from justin_good r3 whe

31、re r.justin_good_id not in4 (select pc.id from justin_count pc)5 ;COUNT(*)-7229SQL alter session set events 10053 trace name context off;摘录Column Usage Monitoring is ON: tracking level = 1*COST-BASED QUERY TRANSFORMATIONS*FPD: Considering simple filter push (pre rewrite) in SEL$1 (#0)FPD:Current whe

32、re clause predicates in SEL$1 (#0) :R.justin_good_idALL (SELECT PC.ID FROM justin.justin_count PC)Registered qb: SEL$1 0x97181720 (COPY SEL$1)signature(): NULLRegistered qb: SEL$2 0x971ef468 (COPY SEL$2)signature(): NULL*Cost-Based Subquery Unnesting*SU: No subqueries to consider in query block SEL$

33、2 (#2).SU: Considering subquery unnesting in query block SEL$1 (#1)SU: Performing unnesting that does not require costing.SU: Considering subquery unnest on SEL$1 (#1).SU:Checking validity of unnesting subquery SEL$2 (#2)SU:SU bypassed: No correlation to immediate outer subquery.SU:Validity checks f

34、ailed.-试图展开子查询，但是同外部查询的关系验证失败*Cost-Based Complex View Merging*CVM: Finding query blocks in SEL$1 (#1) that are valid to merge.*Set-Join Conversion (SJC)*SJC: Considering set-join conversion in SEL$1 (#1).SJC: Considering set-join conversion in SEL$2 (#2).*Predicate Move-Around (PM)*PM: Considering p

35、redicate move-around in SEL$1 (#1).PM:Checking validity of predicate move-around in SEL$1 (#1).PM:PM bypassed: Outer query contains no views.JPPD: Checking validity of push-down in query block SEL$2 (#2)JPPD:No view found to push predicate into.JPPD: Checking validity of push-down in query block SEL

36、$1 (#1)JPPD:No view found to push predicate into.FPD: Considering simple filter push in SEL$1 (#1)FPD:Current where clause predicates in SEL$1 (#1) :NOT EXISTS (SELECT 0 FROM justin.justin_count PC)FPD: Considering simple filter push in SEL$2 (#2)FPD:Current where clause predicates in SEL$2 (#2) :LN

37、NVL(PC.ID:B1)kkogcp: try to generate transitive predicate from check constraints for SEL$2 (#2)predicates with check contraints: LNNVL(PC.ID:B1)after transitive predicate generation: LNNVL(PC.ID:B1)尝试进行谓词转换finally: LNNVL(PC.ID:B1)kkogcp: try to generate transitive predicate from check constraints for SEL$1 (#1)predicates with check contraints: NOT EXISTS (SELECT 0 FROM justin.justin_count PC)after transitive predicate generation