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的使用