我测试了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查询时间与逻辑读都基本一样。而且表之间的连接顺序，单表访问方式也一样。

ITPub博客

NOT IN 与NOT EXISTS的区别何在？

最新文章