ITPub博客

首页 > Linux操作系统 > Linux操作系统 > in、exists与索引

in、exists与索引

原创 Linux操作系统 作者:space6212 时间:2019-05-17 20:21:05 0 删除 编辑

这篇文章主要讨论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的使用

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/231499/viewspace-63820/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2005-01-25

  • 博文量
    245
  • 访问量
    168654