合 Oracle为何在查询中索引未被使用 (文档 ID 1549181.1)
- 简介
- 为何在查询中索引未被使用 (Doc ID 1549181.1)
- 快速检查
- 表上是否存在索引？
- 索引是否应该被使用？
- 索引本身的问题
- 索引列或者索引的前置列是否在单表（non-join）查询的 Where 条件中（predicate list）？
- 索引列是否用在连接谓词中（join predicates）？
- 索引列在 IN 或者多个 OR 语句中？
- 索引列是否被函数修改？
- 隐式类型转换（implicit type conversion）是什么？
- 是否在语义（semantically）上无法使用索引？
- 错误类型的索引扫描？
- 是否索引列为可空？
- NLS_SORT是否设置为二进制（BINARY）？
- 是否使用的是不可见索引（invisible indexes）？
- 优化器和成本计算相关问题
- 是否存在准确且合适的统计信息（Statistics）？
- 一个索引是否与其它的索引有相同的等级或者成本（cost）？
- 索引的选择度不高？
- 在总体成本中，表扫描的成本占大部分
- 访问空索引并不意味着比访问有值的索引高效。
- 参数设置
- 其它问题
- 是否使用了视图/子查询？
- 是否存在远程表(remote table)？
- 是否使用并行执行（PX）?
- 是否是包含了子查询的Update语句？
- 查询是否使用了绑定变量？
- 查询是否引用了带有延迟约束的列?
- 索引提示（hint）不工作
- 参考
简介
“为什么索引没有被使用”是一个涉及面较广的问题。有多种原因会导致索引不能被使用。首要的原因就是统计信息不准，第二原因就是索引的选择度不高，使用索引比使用全表扫描效率更差。还有一个比较常见的原因，就是对索引列进行了函数、算术运算或其他表达式等操作，或出现隐式类型转换，导致无法使用索引。还有很多其它原因会导致不能使用索引，这个问题在MOS（MOS即My Oracle Support）“文档1549181.1为何在查询中索引未被使用”中有非常详细的解释上了。
下面是一些非常有用的检查项目。
一、快速检查
表上是否存在索引？
索引是否应该被使用？
二、索引本身的问题
索引的索引列是否在WHERE条件中（Predicate List）？
索引列是否用在连接谓词中（Join Predicates）？
连接顺序（Join Order）是否允许使用索引?
索引列是否在IN或者多个OR语句中？
是否对索引列进行了函数、算术运算或其他表达式等操作？
索引列是否出现了隐式类型转换（Implicit Type Conversion）？
是否在语义（Semantically）上无法使用索引？
错误类型的索引扫描？
索引列是否可以为空？
NLS_SORT是否设置为二进制（BINARY）？
是否使用的是不可见索引（Invisible Indexes）？
三、优化器和成本计算相关问题
是否存在准确且合适的统计信息（Statistics）？
一个索引是否与其它的索引有相同的等级或者成本（Cost）？
索引的选择度是否不高？
在总体成本中，表扫描的成本是否占大部分？
访问空索引并不意味着比访问有值的索引高效？
参数设置是否正确？
四、其它问题
是否存在远程表（Remote Table)？
是否使用了并行执行（PX）？
是否包含了子查询的UPDATE语句？
查询是否使用了绑定变量？
查询是否引用了带有延迟约束的列？
索引提示（Hint）是否不工作？
索引列是否使用了前置通配符（%）？
索引列是否使用了非等值连接符？
是否在WHERE子句中对索引列进行了IS NULL值判断？
是否查询转换失败导致不能选择索引？
是否使用了视图或子查询？
为何在查询中索引未被使用 (Doc ID 1549181.1)
“为什么索引没有被使用”是一个涉及面较广的问题。有很多种原因会导致索引没有被使用。下面是一些非常有用的检查列表。请点击下面链接来查看文章的具体内容：
快速检查
表上是否存在索引？
检查您认为应该通过索引访问的表上是否真的有定义索引。那些索引可能已经被删掉或者在创建的时候就失败了 – 比如一种可能的场景是，在对表做导入或 load 操作后，由于软件或人为错误造成索引没有被创建。下面的语句可以用来检查索引是否存在。
1SELECT index_name FROM user_indexes WHERE table_name = &Table_Name;索引是否应该被使用？
Oracle 不会仅仅因为有索引存在就一定要使用索引。如果一个查询需要检索出这个表里所有的记录（比如说表之间做连接操作），那为什么还要既访问索引的所有数据又访问表的所有数据呢？在这种情况下只访问表的数据会更快。对所有的查询 Oracle Optimizer 会基于统计信息来计算各种访问路径，包括索引，从而选出最优的一个。
索引本身的问题
索引列或者索引的前置列是否在单表（non-join）查询的 Where 条件中（predicate list）？
如果不是，至少需要索引前置列在查询谓词列表中，查询才能使用索引。（例外：请见下面的 Skip Scan）。
示例:
在列 EMP.EMPNO 上定义了单列索引 EMPNO_I1，同时在列 EMP.EMPNO 和 EMP.DEPT 上定义了联合索引 EMPNO_DEPT_I2（EMP.EMPNO为索引前置列）。那么必须在查询谓词列表中（where从句）使用列 EMP.EMPNO，优化器才能使用这两个索引中的某一个。
1SELECT ename, sal, deptno FROM emp WHERE empno<100;例外:
- 只要索引中包含查询所需的所有列, 而且至少有一个索引列中含有非空约束，CBO 就能够使用索引快速全扫描(INDEX_FFS)。执行 INDEX_FFS 不需要索引前置列。需要注意的是 INDEX_FFS 不能保证返回的行是排序的。结果的顺序是与读取索引块的顺序一致的，只有当使用了 'order by' 子句时才能保证结果是排序的。请参照:
Document 344135.1 Ordering of Result Data
Document 70135.1 Index Fast Full Scan Usage To Avoid Full Table Scans- CBO 能使用 Index Skip Scan (INDEX_SS). 执行 INDEX_SS 不需要索引前置列。请参照:
Document 212391.1 Index Skip Scan Feature
- CBO 能够选用一个索引来避免排序，但是索引列必须存于在 order by 子句中才可以。
请参照
Document 67409.1 When will an ORDER BY use an Index to Avoid Sorting?
Document 10577.1 Driving ORDER BY using an Index索引列是否用在连接谓词中（join predicates）？
例如，下面这个连接谓词定义了如何在表 emp 和 dept 的 deptno 列上做连接：
1emp.deptno = dept.deptno如果索引列是连接谓词的一部分，那么查询在执行时使用了哪种类型的连接？
哈希/排序合并连接（Hash / Sort Merge Join）: 对于哈希连接和排序合并，在连接执行的时候，外部表的信息还没有获得，因此无法进行对内部表的行检索。它的处理方式是将外部表和内部表分别查询后将结果合并。哈希连接和排序合并的内部表不能通过连接的索引列单独被访问。这是连接类型的执行机制的限制。嵌套循环连接有所不同，它们允许通过索引查询内部表的连接列。
嵌套循环连接（Nested Loops Join）:嵌套循环连接读取外部表，然后利用所收集的信息访问内部表。该算法允许对内部表基于索引进行查询。
只有嵌套循环连接（Nested loops join）允许索引在内部表中仅基于连接列进行查找。
另外，连接的顺序（join order）是否允许使用索引?
一个嵌套循环连接的外部表必须已经访问过，才可以在内部表中使用索引。查看 explain plan，以确定哪些访问路径已经使用。由于这个限制，表的连接顺序是很重要的。
例如：如果我们通过"emp.deptno = dept.deptno"来对 EMP 和 DEPT 做连接，并且在 EMP.DEPTNO 有一个索引，并假设查询中没有与 EMP.DEPTNO 相关的其他谓词，EMP 是在 DEPT 前被访问，然后没有值可用于在 EMP.DEPTNO 索引中查询。在这种连接顺序下，要想使用这个索引我们只能使用全索引扫描或索引快速全扫描。在这种情况下，全表扫描（FTS）的成本可能更小。
索引列在 IN 或者多个 OR 语句中？
比如：
本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务，私聊QQ646634621或微信dbaup66，谢谢！
