首先来看两个sql,返回结果相同,但是耗时差别很大
SQL> 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(*)
----------
7229
Executed in 3.437 seconds
SQL> 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(*)
----------
7229
Executed in 128.203 seconds
再来看一下它们的执行计划
使用not exist的语句cost为3452,而not in 的却达到14216
SQL> explain plan for 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);
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 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 | 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:00:01 |
-------------------------------------------------------------------------------------
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.
可以看到使用not exist的sql采用了hash join anti,而not in的却使用了filter
改写一下语句,确保justin_good_id不会在查询中返回NULL
SQL> set linesize 300
SQL> explain plan 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.
ts