在索引和表扫描之间选择
用户常常对此感到困惑，为什么在他们认为优化器应该使用索引时，它却选择全表扫描。本章后面的一节，介绍了优化器优先选择全表扫描而不是访问索引的几种情况。不过，在深入介绍这些情况之前，首先来了解一下基于成本的优化器工作的原理。
基于成本的优化器的任务是，从一组可能的计划中选择最佳或最优的执行计划。基于成本的优化器首先利用表或索引的行数，每列的不同值的数量以及更多其他信息，来估计可供选择的执行计划的成本。然后，它采用成本最低的执行计划。
出于演示的目的，让我们集中注意力研究确定查询成本最重要的因素之一：表的总行数和优化器需要从该表中读取的行数。几乎可以肯定，如果表的体积非常小，那么优化器将把它完全读入。假定某个表包含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%)时，优化器也可能执行全表扫描。