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

说明

相关文章链接：

你创建的索引为什么不工作了？（一）： http://blog.itpub.net/69992972/viewspace-2766087/

你创建的索引为什么不工作了？（二）： http://blog.itpub.net/69992972/viewspace-2766688/

你创建的索引为什么不工作了？（三）： http://blog.itpub.net/69992972/viewspace-2766797/

前言

你是否有时会困惑：你刚创建的索引怎么不工作了？如果你正因此感到困惑的同时看到这篇文章，那么恭喜你，你的困惑即将被解决！我将常见的问题一一列出，并提供了解决办法，下面和我一起来看看你的困惑是哪种吧！

索引未被使用的可能原因（二） 1.统计信息是否准确 ？ 任何SQL语句在执行前都需要检查统计信息是否是准确且最新 ，因为从10g开始，优化器默认是基于成本的优化器（CBO），而CBO又依赖于准确的、最新的和完整的统计信息来确定一个特定查询的最佳执行计划。所以一旦使用CBO，必须确保统计信息已经收集。如果没有统计信息， CBO 将使用预定义的统计信息或者level 2级别的动态采样，这样是很可能导致统计信息不准，进而可能导致不走索引。 注： （1）CBO 会根据开销(COST)来决定使用不同的索引。除了基表和索引的信息之外，如果说在某些列上数据分布是不均匀且倾斜的，那么还需要收集这些列的数据的分布，比如直方图。 （2）level 2动态采样规则（Oracle默认采样级别，一共有11级）：对没有收集过统计信息的表启用动态采样，并且采样的块数是64个。如果表的块数小于64个，则会收集这张表的所有块。 在一般情况下，对象的数据或结构的改变会使以前的统计信息不准确，因此应该重新收集新的统计信息（ 根据我的经验，数据变化量高于10%的时候，统计信息就会过期）。安装新补丁集后，也建议重新收集统计信息。 表访问最佳效果是统计信息是在相同版本的数据库中生成的，不应该跨版本。 2.一个索引是否与其它的索引有相同的等级或者成本 ？ 对于相同成本（COST）的索引，CBO会使用多种办法将不同的索引区分开，如将索引名称按字母顺序排序，完全匹配的索引扫描会选择更大的NDK(不同键值的个数)的索引（不适用于索引快速全扫描—FFS）或选择叶块数量较少的索引。但是一般很少发生这种情况。   3.索引的选择性是否很低？ （1）索引的选择性低（数据重复率高了）， 使用它可能不是一个好的选择，因为 列数据不是平均分布的。 （2）CBO会假定列数据不会倾斜，并均匀分布。如果不是这样，那么统计信息可能没有反映真实情况，那么即使某些值的选择性高，索引也会因为整个列的选择性不高而不适用索引，那么应考虑收集直方图或者使用HINT。 （3）统计信息不准确导致索引看起来选择性不高而不被选择。需要 收集更精确的统计值。 对于数据分布不均匀的列考虑收集列的统计信息 。 4.索引回表扫描的成本是否太高？ 当使用索引的时候，优化器需要再次去检索表本身来找到索引中不存在的字段的值，这个操作就是回表，而且 回表比检索索引本身的开销要大很多。由于优化器是基于总体的成本来计算执行计划，如果优化器发现回表成本很大，并且超过了某个阀值，就会考虑其他的访问路径。 比如：

SELECT empno FROM emp WHERE empno=7876

这条语句可能会使用基于列 empno的索引，因为所有需要的数据都存放在索引中所以不需要再回表访问。 稍微改变下：

SELECT ename FROM emp WHERE empno=7876

ename字段没有存放在索引中，需要回表访问，并且检索ename的开销会随着查询返回记录 条数的增加而变得昂贵。 注：优化器使用"Clustering Factor"来判断如果使用index还需要额外对表做多少次访问。 5.访问空索引并不一定比访问有值的索引高效 Reorganize, truncate或delete操作不一定会影响SQL语句执行的成本。但是delete操作并不会从对象中真正释放空间，所以delete操作不会重置对象的高水位线，但truncate操作会重置高水位线。 空块的存在会使索引/表扫描的成本比实际应该的成本高 。删掉并重建会重组对象的结构从而有可能会有帮助，但也有可能变坏。这类问题通常出现在比较两个有相同数据的不同系统查询性能时。 6.参数默认值被改动 改变某些参数的默认设置可能会影响索引的使用。比如，在大多数情况下都建议使用DB_FILE_MULTIBLOCK_READ_COUNT和 OPTIMIZER_INDEX_COST_ADJ的默认值。除非某些特定的操作有特定的建议，使用其它值会使索引的成本不可预测地 减少或变大从而极大的降低查询的性能，因此 不建议改动这些默认参数。