【TUNE_ORACLE】你创建的索引为什么不工作了？（一）

说明相关文章链接：你创建的索引为什么不工作了？（一）： http://blog.itpub.net/69992972/viewspace-2766087/ 你创建的索引为什么不工作了？（二）： http://blog.itpub.net/69992972/viewspace-2766688/ 你创建的索引为什么不工作了？（三）： http://blog.itpub.net/69992972/viewspace-2766797/ 前言 你是否有时会困惑：你刚创建的索引怎么不工作了？如果你正因此感到困惑的同时看到这篇文章，那么恭喜你，你的困惑即将被解决！我将常见的问题一一列出，并提供了解决办法，下面和我一起来看看你的困惑是哪种吧！ 索引未被使用的可能原因（一） 1. 表上是否真的存在索引？ 检查被访问的表上是否真的有定义索引。因为那些索引可能已经被删掉或者在创建的时候就失败了。 比如，在对表做导入或load操作后，由于软件或人为错误造成索引没有被创建。下面的语句可以用来检查索引是否存在。

SELECT index_name FROM user_indexes WHERE table_name = XXX;

2.你创建的索引是否真的应该被使用？ Oracle不会仅仅因为有索引存在就一定要使用索引。如果一个查询需要检索出这个表里所有的记录（比如说表之间进行关联），那为什么还要既访问索引的所有数据又访问表的所有数据呢？在这种情况下只访问表的数据会更快（全表扫描）。对所有的查询Oracle优化器会基于统计信息来计算各种访问路径，包括索引，从而选出最优的一个（统计信息准的话，优化器的方案一般都是最优的， 人为hint操作需慎重）。 3.索引列或者索引的前导列是否在单表查询的 Where条件中？ 如果不是，至少需要索引前导列在查询谓词列表中，查询才能使用索引（除索引跳跃扫描Skip Scan这种方式以外）。 如: 在列EMP.EMPNO 上定义了单列索引 IDX_EMPNO，同时在列EMP.EMPNO和EMP.DEPT上定义了联合索引IDX_EMPNO_DEPT（EMP.EMPNO为索引前导列）。那么必须在查询谓词列表中（where从句）使用列EMP.EMPNO，优化器才能使用这两个索引中的某一个。

SELECT ename, sal, deptno FROM emp WHERE empno < 100;

另外: （1）只要索引中包含查询所需的所有列, 而且至少有一个索引列中含有非空约束，CBO就能够使用索引快速全扫描 (INDEX_FFS)。执行INDEX_FFS不需要索引前置列。需要注意的是 INDEX_FFS不能保证返回的行是已排序的。结果的顺序是与读取索引块的顺序一致的，只有当使用了 'order by' 子句时才能保证结果是排序的。 （2）CBO能使用 Index Skip Scan (INDEX_SS)，执行 INDEX_SS不需要索引前置列，但是这种扫描方式效率很低。 （3）CBO能够选用一个索引来避免排序，但是索引列必须存于在 order by 子句中才可以（利用索引自动排序的特性，可避免执行计划中SORT ORDER BY的出现，提升SQL性能）。 4.索引列是否用在连接谓词中 ？ 举个例子，下面这个连接谓词定义了如何在表 emp 和 dept 的 deptno 列上做连接：

emp.deptno = dept.deptno

如果索引列是连接谓词的一部分，那么查询在执行时使用了哪种类型的连接？ （1）哈希/排序合并连接（Hash / Sort Merge Join，简称SMJ）: 对于哈希连接和排序合并连接，在连接执行的时候，外部表的信息还没有获得，因此无法进行对内部表的行检索。 它的处理方式是将外部表和内部表分别查询后将结果合并。哈希连接和排序合并连接的内部表不能通过连接的索引列单独被访问。这是连接类型的执行机制的限制。嵌套循环连接有所不同，它们允许通过索引查询内部表的连接列。 （2）嵌套循环连接（Nested Loops Join，简称NL）:嵌套循环连接读取外部表，然后利用所收集的信息访问内部表。该算法允许对内部表基于索引进行查询。 只有嵌套循环连接允许索引在内部表中仅基于连接列进行查找。 另外，连接的顺序（join order）是否允许使用索引? 一个嵌套循环连接的外部表必须已经访问过，才可以在内部表中使用索引 。查看explain plan，以确定哪些访问路径已经使用。由于这个限制，表的连接顺序是很重要的。 例如：如果我们通过"emp.deptno = dept.deptno"来对EMP和DEPT做连接，并且在 EMP.DEPTNO有一个索引，并假设查询中没有与 EMP.DEPTNO相关的其他谓词，EMP是在 DEPT前被访问，然后没有值可用于在EMP.DEPTNO索引中查询。在这种连接顺序下，要想使用这个索引我们只能使用全索引扫描或索引快速全扫描。在这种情况下，全表扫描（FTS）的成本可能更小。 5.索引列在 IN 或者多个 OR 语句中吗？ 比如： emp.deptno IN (10,23,34,....) 或 emp.deptno = 10 OR emp.deptno = 23 OR emp.deptno = 34 .... 这种情况下查询可能已经被转化为不能使用索引的语句，所以尽可能少的使用in和or。 6.索引列是否被函数修改？ 索引不能用于被函数修改的列。创建函数索引可以用来解决这个问题。 7.是否出现隐式类型转换 ？ 如果进行比较的两个值的数据类型不同，则 Oracle 必须将其中一个值进行类型转换使其能够比较。这就是所谓的隐式类型转换。 通常当开发人员将数字存储在字符列时会导致这种问题的产生。Oracle 在运行时会强制转化其中一个值，（由于固定的规则）在索引字符列使用to_number。 由于添加函数到索引列所以导致索引不被使用。实际上，Oracle 也只能这么做，类型转换是一个应用程序设计因素。由于转换是在每行都进行的，这会导致性能问题，因此尽量写全转换函数来避免隐式转换。 8.是否在语义上无法使用索引？ 出于对查询整体成本的考虑，一个成本较低的执行计划中可能是无法使用索引的。某索引可能已经被考虑在某种连接排序及方法中，但是成本最低的那个执行计划中却无法从“语义”角度使用该索引。 9.使用了错误类型的索引扫描？ 例如：快速全索引扫描而不是索引范围扫描 这可能是优化器选择了所需的索引，但却使用了用户不希望看到的扫描方法。在这种情况下，利用 INDEX_FFS，INDEX_ASC 和 INDEX_DESC 提示来强制使用需要的扫描类型。 我们还可以定义索引的排序顺序为递增或递减。Oracle对待降序索引就好像它是基于函数的索引，因此与缺省使用的升序的执行计划不同。通过查看执行计划，您看不到使用升序或降序，需要额外检查视图DBA_IND_COLUMNS的'DESCEND'列。 10.是否索引列为可空？ 索引不存储 NULL 值，除非该索引为联合索引（即多列索引），或者它是一个位图索引。 只有至少有一个索引列有值，组合索引才存储空值 。 如，某个含有空值的列想让它单独走索引，可以这么创建组合索引来解决：idx_name( 列名, 任意阿拉伯数字)。 组合索引中尾部的空值也会被存放在索引中。如果所有列的值都为空，这行将不会存储在索引中。由于索引中缺乏 NULL 值，那么一些结果中可能会返回 NULL 值（如count）的操作可能会被禁用索引。这是因为优化器不能保证在单独使用索引时可以获得准确的信息。 位图索引允许存储空值 。因此优化器会使用这些索引，无论它们的结果可信与否。索引上的空值有时很有用，特别对于某些类型的 SQL 语句，如与聚合函数 COUNT 查询。如:

SELECT count(*) FROM emp;

11.NLS_SORT 是否设置为二进制 ？ 如果 NLS_SORT 未设置为二进制，索引将不会被使用。这是因为索引是基于 Key 值的二进制顺序来建立的（presorted使用二进制值）。无论优化器设置为何种方法，NLS_SORT 不是二进制时，将使用全表扫描。 12.是否使用的是不可见索引（ invisible indexes ）？ 从 Oracle 11gR1开始，可以创建不可见索引或将一个已经存在的索引标记为不可见（就可以简单认为该索引已经被“删除”了，但是还占用着空间）。 这种方式可以在白天屏蔽掉不想要的索引，重建新索引即可，而不是去直接删除再去创建索引（因为这样导致性能降低，影响业务），等晚上系统空闲了再删除不可见索引即可。 优化器不会考虑不可见索引，除非在 session或 system级将参数 OPTIMIZER_USE_INVISIBLE_INDEXES设置为TRUE。但是DML操作还是会维护这些不可见索引的。