一次针对Not in的优化--如何让其选择hash join anti

首先来看两个sql，返回结果相同，但是耗时差别很大

SQL> select count(*)
  2             fromjustin_good r
  3            where notexists
  4                 (select 'x' from justin_count pc where pc.id =r.justin_good_id)
  5  ;

  COUNT(*)
----------
      7229

Executed in 3.437 seconds

SQL> select count(*)
  2             fromjustin_good r
  3            wherer.justin_good_id not in
  4                 (select pc.id from justin_count pc)
  5  ;

  COUNT(*)
----------
      7229

Executed in 128.203 seconds

再来看一下它们的执行计划
使用not exist的语句cost为3452，而not in 的却达到14216
SQL> explain plan for select count(*)
  2             fromjustin_good r
  3            where notexists
  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 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:42

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             fromjustin_good r
  3            wherer.justin_good_id not in
  4                 (select pc.id from justin_count pc);

Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------Planhash value: 4119029611

Id	Operation	Name	Rows	Bytes	Cost(%CPU)	Time
0	SELECT STATEMENT		1	4	14216   (2)	00:00:42
1	SORT AGGREGATE		1	4
2	FILTER					00:00:42
3	TABLE ACCESS FULL	PK11_1	602K	2355K	3442   (2)	00:00:01
4	INDEX FULL SCAN	justin_good	1	5	11   (0)	00:00:42

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Predicate 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.

 

当使用 where a not in (select …)的时候，必须确保该列a的返回值没有null,否则无法进行hash join anti.
解决办法：
1、  为该列加not null约束
2、  改写sql,如not exists

 

当oracle解析where a not in (select )之类的sql时，会查看列a是否存在null，只有当确保不存在的时候，才会考虑将Not in转换为anti-join；然后进行一系列诸如nest loop join,merge join的cost对比，最终敲定执行计划