In和exists使用及效能分析(三)：in和exists的效能分析
本節主要討論in和exists的效能。
三、in和exists的效能分析
3.1 基於8i資料庫使用in和exists
3.1.1資料準備
SQL> create table big_table as select * from dba_objects;
Table created
SQL> insert into big_table select * from big_table;
3160 rows inserted
........
SQL> create table small_table as select * from dba_objects where rownum<50;
Table created
SQL> create index idx_big_table on big_table(object_id);
Index created
SQL> create index idx_small_table on small_table(object_id);
Index created
SQL> analyze table big_table compute statistics;
Table analyzed
SQL> analyze table small_table compute statistics;
Table analyzed
SQL> select count(1) from big_table;
COUNT(1)
----------
202240
SQL> select count(1) from small_table;
COUNT(1)
----------
49
3.1.2 外層資料集大於內層資料集
? 8i CBO下in和exists比較> select count(1) from big_table b where b.object_id in (select object_id from small_table s);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=52 Card=1 Bytes=16)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=52 Card=3136 Bytes=50176)
3 2 VIEW OF 'VW_NSO_1' (Cost=3 Card=49 Bytes=637)
4 3 SORT (UNIQUE) (Cost=3 Card=49 Bytes=147)
5 4 TABLE ACCESS (FULL) OF 'SMALL_TABLE' (Cost=1 Card=
49 Bytes=147)
6 2 INDEX (RANGE SCAN) OF 'IDX_BIG_TABLE' (NON-UNIQUE) (Co
st=1 Card=202240 Bytes=606720)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
115 consistent gets
0 physical reads
0 redo size
366 bytes sent via SQL*Net to client
367 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed> select count(1) from big_table b where exists (select 1 from small_table s where b.object_id=s.object_id);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=363 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=363 Card=1011
2 Bytes=30336)
4 2 INDEX (RANGE SCAN) OF 'IDX_SMALL_TABLE' (NON-UNIQUE) (
Cost=1 Card=1 Bytes=3)
Statistics
----------------------------------------------------------
0 recursive calls
5 db block gets
188435 consistent gets
0 physical reads
0 redo size
366 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
從以上結果可以分析：8i在CBO最佳化器下，如果外層資料集遠大於內層資料集，則in操作的效率遠高於exists的效率。
? 8i RBO下in和exists比較> select /*+ rule */count(1) from big_table b where b.object_id in (select object_id from small_table s);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS
3 2 VIEW OF 'VW_NSO_1'
4 3 SORT (UNIQUE)
5 4 TABLE ACCESS (FULL) OF 'SMALL_TABLE'
6 2 INDEX (RANGE SCAN) OF 'IDX_BIG_TABLE' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
109 consistent gets
0 physical reads
0 redo size
366 bytes sent via SQL*Net to client
378 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed> select /*+ rule */ count(1) from big_table b where exists (select 1 from small_table s where b.object_id=s.object_id);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'BIG_TABLE'
4 2 INDEX (RANGE SCAN) OF 'IDX_SMALL_TABLE' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
5 db block gets
188435 consistent gets
0 physical reads
0 redo size
366 bytes sent via SQL*Net to client
393 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可以看出，當外層資料遠大於內層資料時，在8i RBO最佳化器下，用in的操作比exists效率高很多。
3.1.3 外層資料集小於內層資料集
? 8i CBO下in和exists比較> select count(1) from small_table s where s.object_id in (select object_id from big_table b);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1463 Card=1 Bytes=16
)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (Cost=1463 Card=49 Bytes=784)
3 2 VIEW OF 'VW_NSO_1' (Cost=1463 Card=3159 Bytes=41067)
4 3 SORT (UNIQUE) (Cost=1463 Card=3159 Bytes=9477)
5 4 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=363 Card=
202240 Bytes=606720)
6 2 INDEX (RANGE SCAN) OF 'IDX_SMALL_TABLE' (NON-UNIQUE)
Statistics
----------------------------------------------------------
1021 recursive calls
359 db block gets
5861 consistent gets
604 physical reads
19796 redo size
365 bytes sent via SQL*Net to client
367 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
3 sorts (memory)
1 sorts (disk)
1 rows processed> select count(1) from small_table s where exists (select 1 from big_table b where b.object_id=s.object_id);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=3)
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'SMALL_TABLE' (Cost=1 Card=3 By
tes=9)
4 2 INDEX (RANGE SCAN) OF 'IDX_BIG_TABLE' (NON-UNIQUE) (Co
st=1 Card=65 Bytes=195)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
103 consistent gets
0 physical reads
0 redo size
365 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
1 rows processed
從以上結果可以看出，外層資料集小，內層資料集大的情況時，在oracle 8i CBO環境下，用in的效率比exists低很多。
? 8i RBO下in和exists比較 > select /*+ rule */count(1) from small_table s where s.object_id in (select object_id from big_table s);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS
3 2 VIEW OF 'VW_NSO_1'
4 3 SORT (UNIQUE)
5 4 TABLE ACCESS (FULL) OF 'BIG_TABLE'
6 2 INDEX (RANGE SCAN) OF 'IDX_SMALL_TABLE' (NON-UNIQUE)
Statistics
----------------------------------------------------------
1021 recursive calls
363 db block gets
5859 consistent gets
604 physical reads
20436 redo size
365 bytes sent via SQL*Net to client
379 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
1 sorts (disk)
1 rows processed> select /*+ rule */ count(1) from small_table s where exists (select 1 from big_table b where b.object_id=s.object_id);
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'SMALL_TABLE'
4 2 INDEX (RANGE SCAN) OF 'IDX_BIG_TABLE' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
12 db block gets
103 consistent gets
0 physical reads
0 redo size
365 bytes sent via SQL*Net to client
394 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
由上面的執行計劃可以看到，8i在RBO最佳化器下，如果外層資料集遠小於內層資料集，則用exists的效率遠高於用in。
3.2 基於9i資料庫使用in和exists
3.2.1 準備表及資料
SQL> create table big_table as select * from dba_objects;
Table created
SQL> insert into big_table select * from big_table;
6299 rows inserted
SQL> /
12598 rows inserted
SQL> /
25196 rows inserted
SQL> /
50392 rows inserted
SQL> /
100784 rows inserted
根據exists都執行機理，我們在big_table和small_table都object_id上建立索引，以讓exists高效。下面都比較都是基於關聯欄位有合適索引都基礎上討論的
SQL> create table small_table as select * from dba_objects where rownum<50;
Table created
SQL> create index idx_big_table on big_table(object_id);
Index created
SQL> create index idx_small_table on small_table(object_id);
Index created
SQL> analyze table big_table compute statistics;
Table analyzed
SQL> analyze table small_table compute statistics;
Table analyzed
SQL> select count(1) from big_table;
COUNT(1)
----------
198560
SQL> select count(1) from small_table;
COUNT(1)
----------
49
3.2.2 外層資料集大於內層資料集
? 9i CBO下in和exists比較> select count(1) from big_table b where b.object_id in (select object_id from small_table s);
COUNT(1)
----------
1568
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=42 Card=1 Bytes=6)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (SEMI) (Cost=42 Card=1568 Bytes=9408)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_BIG_TABLE' (NON-UNIQUE)
(Cost=42 Card=198560 Bytes=595680)
4 2 INDEX (RANGE SCAN) OF 'IDX_SMALL_TABLE' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
424 consistent gets
0 physical reads
0 redo size
377 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed> select count(1) from big_table b where exists (select 1 from small_table s where b.object_id=s.object_id);
COUNT(1)
----------
1568
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=42 Card=1 Bytes=6)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (SEMI) (Cost=42 Card=1568 Bytes=9408)
3 2 INDEX (FAST FULL SCAN) OF 'IDX_BIG_TABLE' (NON-UNIQUE)
(Cost=42 Card=198560 Bytes=595680)
4 2 INDEX (RANGE SCAN) OF 'IDX_SMALL_TABLE' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
424 consistent gets
0 physical reads
0 redo size
377 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可見，在9i的CBO最佳化器下，in和exists的執行計劃是一樣的，也就是說，它們效率是一樣的
? 9i RBO下的in和exists比較> select /*+ rule */count(1) from big_table b where b.object_id in (select object_id from small_table s);
COUNT(1)
----------
1568
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS
3 2 VIEW OF 'VW_NSO_1'
4 3 SORT (UNIQUE)
5 4 TABLE ACCESS (FULL) OF 'SMALL_TABLE'
6 2 INDEX (RANGE SCAN) OF 'IDX_BIG_TABLE' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
60 consistent gets
0 physical reads
0 redo size
377 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed> select /*+ rule */ count(1) from big_table b where exists (select 1 from small_table s where b.object_id=s.object_id);
COUNT(1)
----------
1568
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'BIG_TABLE'
4 2 INDEX (RANGE SCAN) OF 'IDX_SMALL_TABLE' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
192915 consistent gets
300 physical reads
0 redo size
377 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可見，在9i的RBO最佳化器下，如果外層資料集遠大於內層資料集，則用in的效率遠遠高於exists。
3.2.3外層資料集小於內層資料集
? 9i CBO下的in和exists比較> select count(1) from small_table s where s.object_id in (select object_id from big_table b);
COUNT(1)
----------
49
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=1 Bytes=6)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (SEMI) (Cost=50 Card=49 Bytes=294)
3 2 INDEX (FULL SCAN) OF 'IDX_SMALL_TABLE' (NON-UNIQUE) (C
ost=1 Card=49 Bytes=147)
4 2 INDEX (RANGE SCAN) OF 'IDX_BIG_TABLE' (NON-UNIQUE) (Co
st=1 Card=198432 Bytes=595296)> select count(1) from small_table s where exists (select 1 from big_table b where b.object_id=s.object_id);
COUNT(1)
----------
49
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=50 Card=1 Bytes=6)
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS (SEMI) (Cost=50 Card=49 Bytes=294)
3 2 INDEX (FULL SCAN) OF 'IDX_SMALL_TABLE' (NON-UNIQUE) (C
ost=1 Card=49 Bytes=147)
4 2 INDEX (RANGE SCAN) OF 'IDX_BIG_TABLE' (NON-UNIQUE) (Co
st=1 Card=198432 Bytes=595296)
從以上結果可以得出結論：9i的CBO最佳化器下，in和exists執行路徑一樣，效率也就是一樣的。
? RBO下的in和exists比較> select /*+ rule */ count(1) from small_table s where s.object_id in (select object_id from big_table b);
COUNT(1)
----------
49
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (AGGREGATE)
2 1 NESTED LOOPS
3 2 VIEW OF 'VW_NSO_1'
4 3 SORT (UNIQUE)
5 4 TABLE ACCESS (FULL) OF 'BIG_TABLE'
6 2 INDEX (RANGE SCAN) OF 'IDX_SMALL_TABLE' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2421 consistent gets
1386 physical reads
0 redo size
376 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed> select /*+ rule */ count(1) from small_table s where exists (select 1 from big_table b where b.object_id=s.object_id);
COUNT(1)
----------
49
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: RULE
1 0 SORT (AGGREGATE)
2 1 FILTER
3 2 TABLE ACCESS (FULL) OF 'SMALL_TABLE'
4 2 INDEX (RANGE SCAN) OF 'IDX_BIG_TABLE' (NON-UNIQUE)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
101 consistent gets
0 physical reads
0 redo size
376 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
可見，在9i的RBO最佳化器下，如果外層資料集遠小於內層資料集，則用exists的效率要遠高於in。
3.3 簡單總結：
1、在相關欄位有合適索引的情況下：
（1）當外層資料集遠大於內層資料集時：
8i RBO/CBO最佳化器下都是in比exists更高效
9i 在RBO下in比exists高效，在CBO下in和exists執行計劃相同，效率也相同。
（2）當外層資料集遠小於內層資料集時：
8i RBO/CBO最佳化器下都是exists比in更高效
9i 在RBO下exists比in高效，在CBO下in和exists執行計劃相同，效率也相同。
2、根據in和exists的原理，不難推斷，如果相關欄位沒有合適索引，得到的結論和有合適索引時一致。
本文是討論的是相對極端的例子，如果外層資料集和內層資料集大小差不多，則in和exists的效率那個更高一點則取決於其他的因素，此時可以考慮用其他方法，如外連線+ null等。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/231499/viewspace-63765/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- In和exists使用及效能分析(二)：exists的使用
- In和exists使用及效能分析(一)：in的使用
- [精選] SQL中的IN與NOT IN、EXISTS與NOT EXISTS的區別及效能分析SQL
- 對線面試官：SQL中的IN與NOT IN、EXISTS與NOT EXISTS的區別及效能分析面試SQL
- exists和not exists及in和not in的用法與區別
- mysql 關於exists 和in分析MySql
- [Oracle] exists 和 not existsOracle
- oracle中關於in和exists，not in 和 not existsOracle
- Oralce 使用SQL中的exists 和not exists 用法詳解SQL
- in/exists和not in/not exists執行效率
- oracle中的exists和not exists和in用法詳解Oracle
- sql中in和exists的原理及使用場景。SQL
- oracle中的exists 和not exists 用法詳解Oracle
- SQL中IN,NOT IN,EXISTS,NOT EXISTS的用法和差別SQL
- in 和 exists區別
- Exists和IN的原理解析
- in，exists和not exists ，not in與null的一些關係記載Null
- 【原始碼】Redis exists命令bug分析原始碼Redis
- exists和連線方式
- 蘊含式（包含EXISTS語句的分析）
- perf及火焰圖的使用，效能分析
- oracle中in和exists的區別Oracle
- mongodb之使用explain和hint效能分析和優化MongoDBAI優化
- MySQL下Limit使用及效能分析MySqlMIT
- [Oracle] minus 和 not exists比較Oracle
- ORACLE 中IN和EXISTS比較Oracle
- 對IN & EXISTS NOT IN & NOT EXISTS的優化優化
- EXISTS、IN、NOT EXISTS、NOT IN的區別(ZT)
- Oracle 中不使用NOT IN 和 NOT EXISTS的另一種方法Oracle
- SQL語句中exists和in的區別SQL
- （轉）ORACLE 中IN和EXISTS的區別Oracle
- in和exists的一些區別
- SQL中IN和EXISTS用法的區別SQL
- EXISTS、IN、NOT EXISTS、NOT IN用法區別
- golang 使用pprof和go-torch做效能分析Golang
- SQL中EXISTS的使用SQL
- (轉)ORACLE 中IN和EXISTS比較Oracle
- Redis效能分析和運維工具Redis運維