我測試了in與exists對於查詢是沒有區別的。不管是在表與表之間的連線方式，還是單表的訪問方式都一致，網上經常說要用exists代替in是錯誤的說法。而not in 與not exists的區別則大相徑庭，not exists操作明顯大大優於not in。
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0    Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
先看看in與exists的實驗。

SQL> select count(*) from a where object_id in (select object_id from b);
  COUNT(*)
----------
129835
已用時間:  00: 00: 00.40
執行計劃
----------------------------------------------------------
Plan hash value: 1819916167
------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |    |    1 | 10 |    |  1538 (2)| 00:00:19 |
| 1 |  SORT AGGREGATE    |    |    1 | 10 |    |          |       |
|*  2 | HASH JOIN SEMI |    | 94312 | 921K|  2160K|  1538 (2)| 00:00:19 |
| 3 | TABLE ACCESS FULL| A | 129K| 633K|    | 482 (2)| 00:00:06 |
| 4 | TABLE ACCESS FULL| B | 209K|  1024K|    | 770 (2)| 00:00:10 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"="OBJECT_ID")

統計資訊
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   4878  consistent gets
      0  physical reads
      0  redo size
      410  bytes sent via SQL*Net to client
      385  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed
SQL> select count(*) from a where  exists (select 1 from b where b.object_id=a.object_id);
  COUNT(*)
----------
129835

已用時間:  00: 00: 00.38
執行計劃
----------------------------------------------------------
Plan hash value: 1298564723
------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time    |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |    |    1 | 10 |    |  1538 (2)| 00:00:19 |
| 1 |  SORT AGGREGATE    |    |    1 | 10 |    |          |       |
|*  2 | HASH JOIN ANTI |    | 35524 | 346K|  2160K|  1538 (2)| 00:00:19 |
| 3 | TABLE ACCESS FULL| A | 129K| 633K|    | 482 (2)| 00:00:06 |
| 4 | TABLE ACCESS FULL| B | 209K|  1024K|    | 770 (2)| 00:00:10 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."OBJECT_ID"="A"."OBJECT_ID")

統計資訊
----------------------------------------------------------
      0  recursive calls
      0  db block gets
   4880  consistent gets
      0  physical reads
      0  redo size
      408  bytes sent via SQL*Net to client
      385  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed
可以看到in與exists查詢時間與邏輯讀都基本一樣。而且表之間的連線順序，單表訪問方式也一樣。

NOT IN 與NOT EXISTS的區別何在？

相關文章