in、exists與索引

這篇文章主要討論in、not in、exists、not exists什麼時候可以使得外層的主查詢用到索引。
先看例子：

suk@SUK> @D:TEMPTEST.SQL
SELECT /*+ INDEX(TEST1) */ * FROM TEST1 WHERE ID IN (SELECT ID FROM TEST2)

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=82 Bytes=213
2)

1 0 HASH JOIN (SEMI) (Cost=29 Card=82 Bytes=2132)
2 1 INDEX (FULL SCAN) OF 'IDX_TEST1' (NON-UNIQUE) (Cost=26 C
ard=82 Bytes=1066)

3 1 TABLE ACCESS (FULL) OF 'TEST2' (Cost=2 Card=82 Bytes=106
6)




SELECT /*+ INDEX(TEST1) */ * FROM TEST1 WHERE ID NOT IN (SELECT ID FROM TEST2)

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=10 Card=4 Bytes=52)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'TEST1' (Cost=2 Card=4 Bytes=52)
3 1 TABLE ACCESS (FULL) OF 'TEST2' (Cost=2 Card=4 Bytes=52)



SELECT /*+ INDEX(TEST1) */ * FROM TEST1 WHERE EXISTS (SELECT 1 FROM TEST2 WHERE TEST1.ID=TEST2.ID)

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=82 Bytes=213
2)

1 0 HASH JOIN (SEMI) (Cost=29 Card=82 Bytes=2132)
2 1 INDEX (FULL SCAN) OF 'IDX_TEST1' (NON-UNIQUE) (Cost=26 C
ard=82 Bytes=1066)

3 1 TABLE ACCESS (FULL) OF 'TEST2' (Cost=2 Card=82 Bytes=106
6)




SELECT /*+ INDEX(TEST1) */ * FROM TEST1 WHERE NOT EXISTS (SELECT 1 FROM TEST2 WHERE TEST1.ID=TEST2.ID)

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=6 Card=4 Bytes=52)
1 0 FILTER
2 1 TABLE ACCESS (FULL) OF 'TEST1' (Cost=2 Card=4 Bytes=52)
3 1 INDEX (RANGE SCAN) OF 'IDX_TEST2' (NON-UNIQUE) (Cost=1 C
ard=1 Bytes=13)




ALTER TABLE TEST1 MODIFY ID NOT NULL

表已更改。

SELECT /*+ INDEX(TEST1) */ * FROM TEST1 WHERE ID IN (SELECT ID FROM TEST2)

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=82 Bytes=213
2)

1 0 HASH JOIN (SEMI) (Cost=29 Card=82 Bytes=2132)
2 1 INDEX (FULL SCAN) OF 'IDX_TEST1' (NON-UNIQUE) (Cost=26 C
ard=82 Bytes=1066)

3 1 TABLE ACCESS (FULL) OF 'TEST2' (Cost=2 Card=82 Bytes=106
6)




SELECT /*+ INDEX(TEST1) */ * FROM TEST1 WHERE ID NOT IN (SELECT ID FROM TEST2)

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=4 Bytes=52)
1 0 INDEX (FULL SCAN) OF 'IDX_TEST1' (NON-UNIQUE) (Cost=26 Car
d=4 Bytes=52)

2 1 TABLE ACCESS (FULL) OF 'TEST2' (Cost=2 Card=4 Bytes=52)



SELECT /*+ INDEX(TEST1) */ * FROM TEST1 WHERE EXISTS (SELECT 1 FROM TEST2 WHERE TEST1.ID=TEST2.ID)

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=29 Card=82 Bytes=213
2)

1 0 HASH JOIN (SEMI) (Cost=29 Card=82 Bytes=2132)
2 1 INDEX (FULL SCAN) OF 'IDX_TEST1' (NON-UNIQUE) (Cost=26 C
ard=82 Bytes=1066)

3 1 TABLE ACCESS (FULL) OF 'TEST2' (Cost=2 Card=82 Bytes=106
6)




SELECT /*+ INDEX(TEST1) */ * FROM TEST1 WHERE NOT EXISTS (SELECT 1 FROM TEST2 WHERE TEST1.ID=TEST2.ID)

執行計劃
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=4 Bytes=52)
1 0 INDEX (FULL SCAN) OF 'IDX_TEST1' (NON-UNIQUE) (Cost=26 Car
d=4 Bytes=52)

2 1 INDEX (RANGE SCAN) OF 'IDX_TEST2' (NON-UNIQUE) (Cost=1 C
ard=1 Bytes=13)


從上面的測試不難得出結論：
單列索引：
1、如果關聯的列沒有not null約束，in和exists有可能用到索引；not in和not exists不可能用到索引
2、如果關聯的列有not null約束，in、not in、exists、not exists都有可能用到索引
組合索引：
1、如果關聯的列都沒有not null約束，in和exists有可能用到索引；not in和not exists不可能用到索引
2、如果關聯的列至少有一個列有not null約束，in、not in、exists、not exists都有可能用到索引

如果理解in、not in、exists、not exists的本質，則不難得出上面的結論。參考：
In和exists使用及效能分析(三)：in和exists的效能分析
In和exists使用及效能分析(二)：exists的使用
In和exists使用及效能分析(一)：in的使用