Oracle在索引和表扫描之间选择

在索引和表扫描之间选择
用户常常对此感到困惑,为什么在他们认为优化器应该使用索引时,它却选择全表扫描。本章后面的一节,介绍了优化器优先选择全表扫描而不是访问索引的几种情况。不过,在深入介绍这些情况之前,首先来了解一下基于成本的优化器工作的原理。

基于成本的优化器的任务是,从一组可能的计划中选择最佳或最优的执行计划。基于成本的优化器首先利用表或索引的行数,每列的不同值的数量以及更多其他信息,来估计可供选择的执行计划的成本。然后,它采用成本最低的执行计划。

出于演示的目的,让我们集中注意力研究确定查询成本最重要的因素之一:表的总行数和优化器需要从该表中读取的行数。几乎可以肯定,如果表的体积非常小,那么优化器将把它完全读入。假定某个表包含10000000(1000万)行,而表使用了100000个表块,因为每个块平均包含约100行。现在,在此表上创建索引,大约需要20000个叶块来存储每个索引列的索引条目。索引比表需要的块更少,因为它只保存一个列的值(和相关表列的rowid)。假设该索引的高度为3,这意味着它的blevel为2,并且每个索引列有100个不同的值,这些值的分布是均匀的。因此,该索引的每个索引值将出现100000次(1000万除以100)。下面用如下测试查询来演示优化器如何在多个执行计划中做选择:

SQL>select * from test_table where test_code='ABCDE';

优化器选反的到底是索引扫描还是全表扫描?为什么?让我们先来分析索引访问的成本。由于test_code列上的索引在所有可能值中是均匀分布的,优化器需要从test_code列上索引中的100个不同值里选择一个,这相当于索引数据的百分之一。为此,数据库需要先读根块和分支块(在这个例子中,blevel=2)。因此,优化器首先记录读取这两个块的成本。下一步,数据库读取索引叶块的1%,这相当于(20000 *0.01)=200个叶块。因此,对于索引读取,就需要访问202个索引块。

因为查询请求了表中的所有列值,所以接下来数据库必须读表中的行。在这里,关键的变量是该索引的聚簇因子,即表中索引列的值的聚集情况。索引列的值聚集程序越高,读所有必要的表行需要访问的块就会越少。例如,聚簇因子是所有可能值中最差的。

这意味首它与表中的行数(10000000)几乎相同。对于表中包含100行的100000个数据块中的每一个,数据库选择1行选择10,或从每个数据块选择1%。因此,访问表中数据的总成本将是选择性乘以聚簇因子,0.01*10000000=100000。因此,以索引为基础的读取操作的近似成本是,202个索引块访问加上100000个表块访问。得到100202块的巨大总成本。

在涉及全表扫描的成本时,请记住,全表扫描使用多块读,而不像索引的读取总是使用单块I/O。在这个例子中,假定每个表块包含100行,在一次全表扫描中数据库需要扫描大约100000个数据块。假设multi_block_read_count的值被设置为10。届时数据库将必须执行共100000/10次读取,即10000次读取。如果想要更精确地计算,也可以在总数中添加段头块的读取,因此它的值是10001。

显然,在这种情况下,甚至在假定可能出现最差的索引聚簇因子后,全表扫描的成本还是要低得多(10001次块读取与索引访问的100202次块读取相比)。此外,还可以并行执行全表扫描,使它执行得更快。请注意,在前面的例子中,查询只获取了一个大表的1%的数据,但全表扫描成本要低得多。这个简单的演示表明,优化器选择索引或全表扫描,并不只是依赖于查询需要检索的行占全行数据的百分比。相反,它还依赖于其他关键因素,如数据的分布,表和叶块的数量,表块中行的平均数量,索引叶块中叶条目的平均数量,该索引的聚簇因子和多块读取计数(multi_block_read_count)的大小。在优化器决定究竟使用索引还是全表扫描时,想要完全依靠某种神奇的比率(即查询所应检索的行与总行数的百分比,如1%,5%,10%,25%或50%)来决策是根本不现实的。这个简单的例子表明,即使查询要检索的行数只占某个表总行数很小的一部分(1%)时,优化器也可能执行全表扫描。

Leave a Reply

Your email address will not be published.

Proudly powered by WordPress | Indrajeet by Sus Hill.