對於in和existsnot in和not exists還是有很多的人有疑惑更有甚者禁用not in所有的地方都要用not exists它真的高效嗎？通過下面的使用我們來證明
先制造一些數據
SQL> drop table test purge;
SQL> drop table test purge;
SQL> create table test as select * from dba_objects where rownum <=;
SQL> create table test as select * from dba_objects;
SQL> exec dbms_statsgather_table_stats（usertest）
SQL> exec dbms_statsgather_table_stats（usertest）
SQL> set autotrace traceonly
in和exists原理及性能實驗
SQL> select * from test t where tobject_id in （select tobject_id from test t）
已選擇行
執行計劃
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
| | SELECT STATEMENT | | | | （）| :: |
|* | HASH JOIN SEMI | | | | （）| :: |
| | TABLE ACCESS FULL| TEST | | | （）| :: |
| | TABLE ACCESS FULL| TEST | | K| （）| :: |
Predicate Information （identified by operation id）
access（TOBJECT_ID=TOBJECT_ID）
統計信息
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts （memory）
sorts （disk）
rows processed
SQL> select * from test t
where exists （select from test t where tobject_id = tobject_id）
已選擇行
執行計劃
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
| | SELECT STATEMENT | | | | （）| :: |
|* | HASH JOIN SEMI | | | | （）| :: |
| | TABLE ACCESS FULL| TEST | | | （）| :: |
| | TABLE ACCESS FULL| TEST | | K| （）| :: |
Predicate Information （identified by operation id）
access（TOBJECT_ID=TOBJECT_ID）
統計信息
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts （memory）
sorts （disk）
rows processed
結論在oracle g中in 和 exists其實是一樣的原理就是兩張表做HASH JOIN SEMI也可以通過事件看到兩條sql語句最終轉換成同一條sql
not in和not exists原理及性能實驗
not exists 比 not in效率高的例子
SQL> select count（*） from test where object_id not in（select object_id from test）
執行計劃
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
| | SELECT STATEMENT | | | | （）| :: |
| | SORT AGGREGATE | | | | | |
|* | FILTER | | | | | |
| | TABLE ACCESS FULL| TEST | | | （）| :: |
|* | TABLE ACCESS FULL| TEST | | | （）| :: |
Predicate Information （identified by operation id）
filter（ NOT EXISTS （SELECT /*+ */ FROM TEST TEST WHERE
LNNVL（OBJECT_ID<>:B）））
filter（LNNVL（OBJECT_ID<>:B））
統計信息
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts （memory）
sorts （disk）
rows processed
SQL> select count（*） from test t where not exists
（select from test t where tobject_id=tobject_id）
執行計劃
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
| | SELECT STATEMENT | | | | （）| :: |
| | SORT AGGREGATE | | | | | |
|* | HASH JOIN ANTI | | | | （）| :: |
| | TABLE ACCESS FULL| TEST | | | （）| :: |
| | TABLE ACCESS FULL| TEST | | K| （）| :: |
Predicate Information （identified by operation id）
access（TOBJECT_ID=TOBJECT_ID）
統計信息
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts （memory）
sorts （disk）
rows processed
not in比not exists 效率高的例子
SQL> Set autotrace off
SQL> drop table test purge;
表已刪除
SQL> drop table test purge;
表已刪除
SQL> create table test as select * from dba_objects where rownum <=;
表已創建
SQL> create table test as select * from dba_objects;
表已創建
SQL> Insert into test select * from dba_objects;
已創建行
SQL> Insert into test select * from test;
已創建行
SQL> Insert into test select * from test;
已創建行
SQL> Commit;
提交完成
SQL> exec dbms_statsgather_table_stats（usertest）
PL/SQL 過程已成功完成
SQL> exec dbms_statsgather_table_stats（usertest）
PL/SQL 過程已成功完成
SQL> Set autotrace traceonly
SQL> select count（*） from test where object_id not in（select object_id from test）
執行計劃
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
| | SELECT STATEMENT | | | | （）| :: |
| | SORT AGGREGATE | | | | | |
|* | FILTER | | | | | |
| | TABLE ACCESS FULL| TEST | | | （）| :: |
|* | TABLE ACCESS FULL| TEST | | | （）| :: |
Predicate Information （identified by operation id）
filter（ NOT EXISTS （SELECT /*+ */ FROM TEST TEST WHERE
LNNVL（OBJECT_ID<>:B）））
filter（LNNVL（OBJECT_ID<>:B））
統計信息
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts （memory）
sorts （disk）
rows processed
SQL> select count（*） from test t where not exists
（select from test t where tobject_id=tobject_id）
執行計劃
Plan hash value:
| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |
| | SELECT STATEMENT | | | | （）| :: |
| | SORT AGGREGATE | | | | | |
|* | HASH JOIN ANTI | | | | （）| :: |
| | TABLE ACCESS FULL| TEST | | | （）| :: |
| | TABLE ACCESS FULL| TEST | K| K| （）| :: |
Predicate Information （identified by operation id）
access（TOBJECT_ID=TOBJECT_ID）
統計信息
recursive calls
db block gets
consistent gets
physical reads
redo size
bytes sent via SQL*Net to client
bytes received via SQL*Net from client
SQL*Net roundtrips to/from client
sorts （memory）
sorts （disk）
rows processed
結論not in 和not exists原理是nestedloops 與HASH JOIN的區別not in中的filter算法類似於nestedloops如果比較兩者的性能就是比較nestedloops 與HASH JOIN的性能差異在本例子中
not in 性能 大於not exists test的數據量條test數量多萬條
not exists 性能 大於not in test的數據量條test數量條
From:http://tw.wingwit.com/Article/program/Oracle/201311/17657.html
