oracle 索引命中条件,Oracle索引命中与扫描规律总结 | 学步园

索引扫描类型(该表取自网络文章)：

index unique scan(索引唯一扫描)通过唯一索引查找一个数值经常返回单个ROWID。如果该唯一索引有多个列组成(即组合索引)，则至少要有组合索引的引导列参与到该查询中，如创建一个索引：create index idx_test on emp(ename, deptno, loc)。则select ename from emp where ename = ‘JACK’ and deptno = ‘DEV’语句可以使用该索引。如果该语句只返回一行，则存取方法称为索引唯一扫描。而select ename from emp where

deptno = ‘DEV’语句则不会使用该索引，因为where子句种没有引导列。如果存在UNIQUE 或PRIMARY KEY 约束(它保证了语句只存取单行)的话，Oracle经常实现唯一性扫描。

index range scan(索引范围扫描)：1.对于unique index来说，如果where 条件后面出现了 ,between ...and...的时候，那么就可能执行index range scan,如果where条件后面是=，那么就会执行index unique scan。

2.对于none unique index来说 如果where 条件后面出现了=,>,

3.对于组合索引来说，如果where条件后面出现了组合索引的引导列，那么可能执行index range scan。

index full scan(索引全扫描)与全表扫描对应，也有相应的全Oracle索引扫描。在某些情况下，可能进行全Oracle索引扫描而不是范围扫描，需要注意的是全Oracle索引扫描只在CBO模式下才有效。 CBO根据统计数值得知进行全Oracle索引扫描比进行全表扫描更有效时，才进行全Oracle索引扫描，而且此时查询出的数据都必须从索引中可以直接得到。

index fast full scan(索引快速全扫描)：如果select 语句后面中的列都被包含在组合索引中，而且where后面没有出现组合索引的引导列，并且需要检索出大部分数据，那么这个时候可能执行index fast full scan。index fast full scan 发生的条件:

1.必须是组合索引。

2.引导列不在where条件中

index skip scan(索引跳跃式扫描)当查询可以通过组合索引得到结果，而且返回结果很少，并且where条件中没有包含索引引导列的时候，可能执行index skip scan

索引跳跃式扫描发生的条件:

1.必须是组合索引。

2.引导列没有出现在where条件中

实验针对数据表：JL_KFBJDY

表索引：PK_JL_KFBJDY(JGBM[varchar2],KFID[number],QYID[number],BJID[number])

------------------------------------------------------------------------------------------------------

用例1：SELECT * FROM PMSADM.JL_KFBJDY WHERE JGBM='17'

命中结果：INDEX RANGE SCAN -> PK_JL_KFBJDY

结果分析：条件字段为唯一索引的前缀，以RANGE SCAN方式命中索引

用例1-1：SELECT * FROM PMSADM.JL_KFBJDY WHERE JGBM='17' AND X=1

命中结果：INDEX RANGE SCAN ->

PK_JL_KFBJDY

结果分析：在用例1的基础上，加上非索引字段X作为条件，不影响索引命中

用例1-2：SELECT * FROM PMSADM.JL_KFBJDY WHERE X=1 AND JGBM='17'

命中结果：INDEX RANGE SCAN ->

PK_JL_KFBJDY

结果分析：在用例1-1的基础上，点到条件顺序，不影响索引命中

------------------------------------------------------------------------------------------------------

用例2：SELECT * FROM PMSADM.JL_KFBJDY WHERE JGBM='17' AND KFID=1

命中结果：INDEX RANGE SCAN ->

PK_JL_KFBJDY

结果分析：条件字段为唯一索引的前缀，以RANGE SCAN方式命中索引

------------------------------------------------------------------------------------------------------

用例3：SELECT * FROM PMSADM.JL_KFBJDY WHERE JGBM='17' AND KFID=1 AND QYID=1

命中结果：INDEX RANGE SCAN ->

PK_JL_KFBJDY

结果分析：条件字段为唯一索引的前缀，以RANGE SCAN方式命中索引

用例3-1：SELECT

* FROM PMSADM.JL_KFBJDY WHEREKFID=1 AND JGBM='17' AND QYID=1

命中结果：INDEX RANGE SCAN ->

PK_JL_KFBJDY

结果分析：在用例3的基础上，调整字段顺序，仍然以RANGE SCAN方式命中索引

用例3-2：SELECT

* FROM PMSADM.JL_KFBJDY WHEREKFID=1 AND X=1 AND JGBM='17' AND QYID=1

命中结果：INDEX RANGE SCAN ->

PK_JL_KFBJDY

结果分析：在用例3-1的基础上，加入非索引字段X，仍然以RANGE SCAN方式命中索引

------------------------------------------------------------------------------------------------------

用例4：SELECT * FROM PMSADM.JL_KFBJDY WHERE JGBM='17' AND KFID=1 AND QYID=1 AND BJID=1

命中结果：INDEX UNIQUE SCAN ->

PK_JL_KFBJDY

结果分析：条件字段为唯一索引字段，命中唯一索引

用例4-1：SELECT

* FROM PMSADM.JL_KFBJDY WHERE KFID=1 AND JGBM='17' AND BJID=1 AND QYID=1

命中结果：INDEX UNIQUE SCAN->

PK_JL_KFBJDY

结果分析：在用例4的基础上，打乱条件的顺序，让然能命中唯一索引，说明顺序无关

------------------------------------------------------------------------------------------------------

用例5：SELECT * FROM PMSADM.JL_KFBJDY WHERE KFID=1 AND QYID=1 AND BJID=1

命中结果：INDEX SKIP SCAN->

PK_JL_KFBJDY

结果分析：条件包含部分索引字段(非前缀)，以SKIP SCAN方式命中索引

------------------------------------------------------------------------------------------------------

用例6：SELECT * FROM PMSADM.JL_KFBJDY WHERE QYID=1 AND BJID=1

命中结果：INDEX SKIP SCAN->

PK_JL_KFBJDY

结果分析：条件包含部分索引字段(非前缀)，以SKIP SCAN方式命中索引

------------------------------------------------------------------------------------------------------

用例7：SELECT * FROM PMSADM.JL_KFBJDY WHERE BJID=1

命中结果：INDEX

SKIP SCAN-> PK_JL_KFBJDY

结果分析：条件包含部分索引字段(非前缀)，以SKIP SCAN方式命中索引

用例7-1：SELECT * FROM PMSADM.JL_KFBJDY WHERE BJID=1 AND X=1 AND Y=1

命中结果：INDEX SKIP SCAN->

PK_JL_KFBJDY

结果分析：在用例7的基础上，加入非索引字段X,Y作为条件，仍然以SKIP SCAN方式命中索引

------------------------------------------------------------------------------------------------------

用例8：SELECT * FROM PMSADM.JL_KFBJDY WHERE JGBM='17' AND KFID='1' AND QYID='1' AND BJID=1

命中结果：INDEX UNIQUE SCAN ->

PK_JL_KFBJDY

结果分析：条件字段为唯一索引字段,KFID和QYID数字字段传递字符串，有类型转换过程，仍然命中唯一索引

用例8-1：SELECT * FROM PMSADM.JL_KFBJDY WHERE JGBM='17' AND KFID='1' AND QYID='1'

命中结果：INDEX RANGE SCAN ->

PK_JL_KFBJDY

结果分析： 在用例8的基础上，只传递唯一索引前缀字段，同时包含类型转换，仍然以RANGE SCAN方式命中索引

用例8-2：SELECT * FROM PMSADM.JL_KFBJDY WHERE JGBM='17' AND KFID='1' AND QYID='1'

命中结果：INDEX RANGE SCAN ->

PK_JL_KFBJDY

结果分析： 在用例8-1的基础上，颠倒字段顺序，仍然以RANGE SCAN方式命中索引

用例8-3：SELECT * FROM PMSADM.JL_KFBJDY WHERE JGBM='17' AND KFID='1' AND QYID='1'

命中结果：INDEX RANGE SCAN ->

PK_JL_KFBJDY

结果分析： 在用例8-2的基础上，去掉前缀字段，以SKIP SCAN方式命中索引

用例8-4：SELECT * FROM PMSADM.JL_KFBJDY WHERE JGBM='17' AND KFID='1' AND QYID='1'

AND X=1

命中结果：INDEX RANGE SCAN ->

PK_JL_KFBJDY

结果分析： 在用例8-3的基础上，加上非索引字段X作为条件，仍然以SKIP SCAN方式命中索引

------------------------------------------------------------------------------------------------------

最终结论：

1.唯一索引的所有字段出现在条件中，会以INDEX UNIQUE SCAN方式命中索引

2.当条件字段中包含索引定义的字段顺序的前缀时，将以INDEX RANGE SCAN方式命中索引

3.当条件字段中包含索引定义的一个或多个字段，但不是前缀字段时，以INDEX SKIP SCAN方式命中索引

4.条件中字段出现的顺序不影响命中

5.条件中出现非索引字段的个数、顺序不影响索引的命中

6.条件中出现数据类型转换不影响索引的命中