Oracle优化器忽略索引的原因

优化器忽略索引的原因
创建索引永远不能保证优化器在评估执行计划时一定会使用索引。如果某个查询从表中选择高比例的行,优化器可能觉得使用全表扫描而不是索引扫描,能更快地得到结果。请记住,数据库使用索引时,它首先查找索引获得rowid,然后使用这些rowid来检索所请求的行。如果查询从表中选择很大比例的行,从而导致要读取表中很大比例的块,那么数据库可能执行全表扫描来避免既读取索引又读取表,因为后者的成本可能比仅仅扫描一次表更高。

优化器是否使用索引取决于多种因素,在本章以下各节将分别对它们进行解释。

1 不同的行数
选择全表扫描或索引扫描的另一关键决定因素是,表中与给定查询谓词相匹配的不同行数占表中总行数的比率。通过查询dba_tables视图的num_rows列可以得到某个表的行数。同样,也可以通过查询dba_tab_columns视图的num_distinct列,得到任意列中不同值的数量。num_rows列与num_distinct列的值越接近,优化器越有可能倾向于访问该列的索引而非执行全表扫描,换句话说,索引的选择性越高,数据库就越有可能使用它。

索引的选择性可能会对数据库是否使用索引产生最大的影响。选择性是指每个列值有多少个不同的值。如果索引是非常有选择性的,那么每个索引条目只有很少几行。另外,如果索引的选择性不强,每个索引条目就会有许多行。

请记住,优化器用列的选择性乘以页块(leaf blocks)的统计数量,来估算在访问索引期间,数据库必须读取多少个索引块。虽然事实上,大多数情况下,高选择性的列确实会使用索引,但并不总是如此,因为使用索引还是全表扫描的最终决定因素是块的选择性。

在Oracle Database 10g版本(但不是在11g版本)中,使用dbms_stats.auto_sample_size常量估计dbms_stats包使用的行数,可能会导致对不同值的数量(NDV)产生错误的估计。如果表很大,而且有相当数量的数据是偏态的,往往就会发生这种情况。因此最好对样本大小使用自己的估计,以获得更准确的NDV值。

2 索引聚簇因子
查询dba_indexes视图的clustering_factor列,可以找到索引的聚簇因子的值。聚簇因子用来衡量表中行的有序程度,这种有序程度是与索引的行相比较而言的。如果聚簇因子接近表中的行数,行就有可能是非常随机地排列的,这种情况下,一个索引块的索引条目对应的数据行不太可能位于相同的数据块中。表的选择性(通过过滤)乘以索引聚簇因子,决定了通过索引访问表的成本。这部分计算中使用的实际上是表的选择性。虽然大部分时间,索引的选择性(乘以leaf_blocks,已在上一节讨论)和表的选择性是相同的,计算它实际上是为了确定需要访问表中的多少数据块。通常情况下,我们都假定索引聚簇因子能保证随机性,但事实并非如此。例如,虽然索引中的每行只指向两个不同的块,但条目是按rowid排序为块1,块2,块1,块2……这种(交替排列的)情况会怎么样呢? 如果索引有10000个条目,聚簇因子是10000,但实际上,将仅访问2个块。因此,聚簇因子的计算不能像大多数人认为的那样,保证数据排列的随机性。

在一个具有“好”的聚簇因子的索引中,具体索引叶块中的索引值指向分布在相同数据块中的行。另一方面,在一个具有“坏”的聚簇因子,它可以用更少的I/O读取数据。而具有相同数量的数据但组织得很差的索引,将需要更大的I/O数量,从一组更多的数据块中读取数据。在这种情况下,关键是要注意,如果每个表只有一个索引,那么它的组织可能是理想的,实际上,这只有在表中的数据按特定的顺序加载时才真正成立。举例来说,或许表是按order_date列的顺序加载的。在这种情况下,order_date上索引的聚簇因子将和表中块的数量几乎相同。但是,因为该表的存储只能按一种顺序排列,所以其他所有索引的顺序都将是“欠优化”的。因此,总而言之,“好”与“坏”更是相对而不是绝对的。这可能就是为什么使用索引的成本计算,由包括表和索引选择性的多个元素组成,以使某一个组成部分不会占非常大的比重。

一条经验法则是:良好的聚簇因子接近表的块数,而糟糕的聚簇因子接近表的行数。

除了这里讨论的两个因素,多块读取数的值对索引使用也有影响。数据库文件多块读取计数(db_file_multiblock_read_count)的值越高,从优化器的角度来看,全表扫描的成本就越低。

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.