Oracle 在查询中索引未被使用的排查步骤

1.快速检查

1.1.表上是否存在索引

select index_name from user_indexes where table_name = <table_name>;

1.2.索引是否应该被使用

Oracle不会仅仅因为有索引存在就一定要使用索引。 如果一个查询需要检索出这个表里所有的记录（比如说表之间做连接操作），那为什么还要既访问索引的所有数据又访问表的所有数据呢？ 在这种情况下只访问表的数据会更快。对所有的查询 Oracle Optimizer 会基于统计信息来计算各种访问路径，包括索引，从而选出最优的 一个。

2.索引本身的问题

2.1.索引列或者索引的前置列是否在单表（non-join）查询的 Where 条件中（predicate list）

如果不是，至少需要索引前置列在查询谓词列表中，查询才能使用索引。

示例：
select ename, sal, deptno from emp where empno<100;
在列 EMP.EMPNO 上定义了单列索引 EMPNO_I1，同时在列 EMP.EMPNO 和 EMP.DEPT 上定义了联合索引 EMPNO_DEPT_I2（EMP.EMPNO为索引前置列）。 那么必须在查询谓词列表中（where从句）使用列 EMP.EMPNO，优化器才能使用这两个索引中的某一个。

2.2.索引列是否用在连接谓词中

下面这个连接谓词定义了如何在表emp和dept的deptno列上做连接： emp.deptno = dept.deptno

如果索引列是连接谓词的一部分，那么查询在执行时使用了哪种类型的连接？
（1）哈希/排序合并连接（Hash/Sort Merge Join）：对于哈希连接和排序合并，在连接执行的时候，外部表的信息还没有获得，因此无法进行对内部表的行检索。
它的处理方式是将外部表和内部表分别查询后将结果合并。哈希连接和排序合并的内部表不能通过连接的索引列单独被访问。
这是连接类型执行机制的限制。嵌套循环连接有所不同，它们允许通过索引查询内部表的连接列。

（2）嵌套循环连接（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）的成本可能更小。

2.3.索引在IN或者多个OR语句中

比如： emp.deptno in (10,20,30,…) 或 emp.deptno = 10 or emp.deptno = 20 or emp.deptno = 30 …
这种情况下查询可能已经被转换为不能使用索引的语句。

2.4.索引列是否被函数修改

索引不能用于被函数修改的列。 函数索引可以解决这个问题。

2.5.索引列是否存在隐式类型转换

如果进行比较的两个值的数据类型不同，则 Oracle 必须将其中一个值进行类型转换使其能够比较，这就是所谓的隐式类型转换。 通常当开发人员将数字存储在字符列时会导致这种问题的产生。Oracle 在运行时会强制转化其中一个值，（由于固定的规则）在索引字符列使用 to_number。 由于添加函数到索引列，所以导致索引不被使用。 实际上，Oracle 也只能这么做，类型转换是一个应用程序设计因素。由于转换是在每行都进行的，这会导致性能问题。

2.6.是否在语义上无法使用索引

出于对查询整体成本的考虑，一个成本较低的执行计划中可能是无法使用索引的。 某索引可能已经被考虑在某种连接排序及方法中，但是成本最低的那个执行计划中却无法从“语义”角度使用该索引。

2.7.错误类型的索引扫描

比如：快速全索引扫描而不是索引范围扫描 这可能是优化器选择了所需的索引，但却使用了不希望的扫描方法。 这种情况下，利用提示强制使用需要的索引扫描类型。

示例：

--对指定的表执行快速全索引扫描
SELECT /* + INDEX_FFS(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';

--对指定表执行索引扫描，如为范围索引扫描，将按索引值的升序扫描索引条目
SELECT /* + INDEX_ASC(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';

--对指定表执行索引扫描，如为范围索引扫描，将按索引值的降序扫描索引条目
SELECT /* + INDEX_DESC(BSEMPMS IN_EMPNAM)*/ * FROM BSEMPMS WHERE DPT_NO='TEC305';

还可以定义索引的排序顺序为递增或递减。
Oracle 对待降序索引就好像它是基于函数的索引，因此与缺省使用的升序的执行计划不同。
通过查看执行计划，您看不到使用升序或降序，需要额外检查视图 DBA_IND_COLUMNS 的’DESCEND’列。

2.8.索引列是否为可空

索引不存储 NULL 值，除非该索引为联合索引（即多列索引），或者它是一个位图索引，位图索引允许存储空值。 只有至少有一个索引列有值，联合索引才存储空值。联合索引中尾部的空值也会被存放在索引中。 如果所有列的值都为空，这行将不会存储在索引中。由于索引中缺乏 NULL 值，那么一些结果中可能会返回 NULL 值（如count）的操作可能会被禁用索引。 这是因为优化器不能保证在单独使用索引时可以获得准确的信息。

2.9.NLS_SORT是否设置为二进制

如果 NLS_SORT 未设置为二进制，索引将不会被使用。 这是因为索引是基于 Key 值的二进制顺序来建立的（pre-sorted使用二进制值）。 无论优化器设置为何种方法，NLS_SORT 不是二进制时，将使用全表扫描。

2.10.是否使用的为不可见索引

从 Oracle Database 11g Release 1开始，可以创建不可见索引或将一个已经存在的索引标记为不可见。 Optimizer不会考虑不可见索引，除非在 session 或 system 级将参数 OPTIMIZER_USE_INVISIBLE_INDEXES 设置为 TRUE。 DML 操作还是会维护这些不可见索引的。

3.优化器和成本计算相关问题

3.1.是否存在准确且合适的统计信息

（1）CBO 依赖于准确的、最新的和完整的统计信息来确定一个特定查询的最佳执行计划。 如果使用CBO，请确保统计信息已经收集。如果没有统计信息，CBO 将使用预定义的统计信息，这样是很可能不会产生良好的计划或让应用程序使用索引。

（2）CBO 会根据开销(COST)来决定使用不同的索引。除了基本的表和索引的信息之外，如果说在某些列上数据分布是不均匀的，那么还需要收集这些列的数据的分布。
在一般情况下，对象的数据或结构的改变会使以前的统计信息不准确，因此应该重新收集新的统计信息。
比如：
对表装载了大量的数据后，需要收集新的统计信息。
安装新补丁集（Patchset）后，也建议重新收集统计信息。表访问最佳效果是统计信息是在相同版本的数据库中生成的。

3.2.一个索引是否与其他的索引有相同的等级或者成本

对于相同开销（COST）的索引，CBO 会使用多种办法将不同的索引区分开，如将索引名称按字母顺序排序，完全匹配的索引扫描会选择更大的NDK(不同键值的个数)的索引（不适用于快速全扫描）或（在 10gR2 及更高版本中）选择叶块数量较少的索引。 一般很少发生这种情况。

3.3.索引的选择度不高

3.3.1.索引的选择度不高，可能不是一个好的选择，列数据不是平均分布的。

3.3.2.CBO 假定列数据不会倾斜，并均匀分布。如果不是这样，那么统计信息可能没有反映真实情况，那么即使某些值的选择度高，索引也会因为整个列的选择度不高而不使用索引。 如果是这种情况，那么应考虑采用直方图记录更准确的列的数据分布或者采用提示（hint）。

3.3.3.统计信息不准确导致索引看起来选择性不高而不被选择

规避方法：
（1）收集更准确的统计值。

（2）对于分布不均匀的列，考虑收集列的统计信息。 比如，对现有的直方图重新分析索引

begin
dbms_stats.gather_schema_stats(ownname => 'SCOTT',
                               estimate_percent => dbms_stats.auto_sample_size,
                               method_opt => 'for all columns size repeat',
                               degree => 7);
end;

（3）使用hint或outline。

3.4.在总体成本中，表扫描的成本占大部分

通常来说，当使用索引的时候，我们需要再次检索表本身来找到索引中不存在的字段的值，这个操作比检索索引本身的开销要大很多。 由于 optimizer 是基于总体的成本来计算执行计划，如果通过索引检索表的成本很大，并且超过了某个阀值，optimizer 就会考虑其他的访问路径。

比如：select empno from emp where empno=5 这条语句可能会使用基于列empno的索引，因为所有需要的数据都存放在索引中，所以不需要对表做额外的访问。

反之，select ename from emp where empno=5
这条语句会需要对表做额外的访问，因为 ename 字段没有存放在索引中。检索 ename 的开销会随着查询返回记录条数的增加而变得昂贵。

3.5.访问空索引并不意味着比访问有值的索引高效

Reorganization，Truncation或删除操作不一定会影响 SQL 语句执行的成本。 需要注意的是删除操作并不会从对象中真正释放空间。也就是说，删除操作不会重置对象的高水位线。 Truncate 操作会重置高水位线。空块的存在会使索引/表扫描的成本比实际应该的成本高。 删掉并重建会重组对象的结构从而有可能会有帮助（也有可能变坏）。 这类问题通常在比较两个有相同数据的不同系统查询性能时更容易看到。

3.6.参数设置

某些参数的设置可能会影响索引的使用。
比如，在大多数情况下都建议使用 DB_FILE_MULTIBLOCK_READ_COUNT 和 OPTIMIZER_INDEX_COST_ADJ 的默认值。
除非某些特定的操作有特定的建议，使用其它值会使索引的成本不现实的减少或变大从而极大的降低查询的性能。
如OPTIMIZER_INDEX_COST_ADJ参数默认为100，值越小，表示查询对索引友好，可能会造成优先使用索引的情况。

4、其他问题

4.1.是否使用了视图/子查询

查询涉及到视图或者子查询时可能会被改写，导致不使用索引（尽管该改写的目标之一是扩展更多的访问路径）。 这些改写(rewrite)一般来说都是合并(merging)操作。

4.2.是否存在远程表

通常远程表不会使用索引。索引在分布式查询中的使用依赖于被发送到远程的查询。 CBO 将评估远程访问的成本，并评估比较发送或者不发送索引的谓词到远程站点的成本。 因此，CBO 可以做出有关远程表上使用索引的更加明智的决定。 一个非常有效的方法就是，在远程建立包含相关谓词的视图并强制使用索引，之后在本地查询中使用这个视图。

4.3.是否使用并行执行

在并行执行时，索引的使用比在串行执行时更加严格。 一个快速检测的方法就是禁用并行，然后查看该索引是否被使用。

4.4.是否包含子查询的update语句

在一些情况下，基于成本的考虑，索引没有被选用是因为它依赖于一个子查询返回的值。 这种情况下，可以使用提示（hint）来强制使用索引。

示例：

UPDATE /*+ ORDERED USE_NL(E) INDEX(E) */ emp e
SET e.empno = e.empno
WHERE e.deptno in (SELECT d.deptno FROM dept d)

4.5.查询是否使用了绑定变量

CBO 对 like 或范围谓词的绑定变量不能产生准确的成本(cost)。这可能会导致索引不被选择。

4.6.查询是否引用了带有延迟约束的列

如果一个表中的某一列上含有延迟约束（比如 NOT NULL）并且这一列上有索引，那么不管这个约束当前是延迟状态或是被显式地设置为立即使用，我们都不会考虑使用这一列上的索引。

4.7.索引（hint）不工作

可以使用表的别名。

有用的 hints:

FIRST_ROWS	相当于提示使用索引。
ORDERED	    强制查询的关联顺序（join order of a query）。Oracle 推荐使用 LEADING hint 因为它更好用。
LEADING	    这个 hint 告诉 optimizer 先使用指定的表做连接。它比 ORDERED 更好用。
INDEX	    强制使用索引扫描， 并禁用快速模式（INDEX_FFS）。
INDEX_FFS	强制使用快速索引扫描INDEX_FFS。
INDEX_ASC	强制使用升序的索引范围扫描（Ascending Index Range Scan）。
INDEX_DESC	强制使用降序的索引范围扫描（Descending Index Range Scan）。

参考MOS：Diagnosing and Understanding Why a Query is Not Using an Index (Doc ID 67522.1)