为什么Oracle不使用我的索引

　　Author: Jonathan Lewis
　　
　　Reference: http://www.jlcomp.demon.co.uk/
　　
　　一. 样例
　　
　　create table t1 as
　　select trunc((rownum-1)/15) n1,
　　trunc((rownum-1)/15) n2,
　　rpad(\'x\', 215) v1
　　from all_objects
　　where rownum <= 3000;
　　
　　create table t2 as
　　select
　　mod(rownum,200) n1,
　　mod(rownum,200) n2,
　　rpad(\'x\',215) v1
　　from all_objects
　　where rownum <= 3000;
　　
　　create index t1_i1 on t1(N1);
　　create index t2_i1 on t2(n1);
　　
　　analyze table t1 compute statistics;
　　analyze table t2 compute statistics;
　　
　　二. 结果:(使用choose进行plan解释得到的结果)
　　
　　1. select * from t1 where n1 = 45;
　　-- use index range scan
　　
　　EXECUTION PLAN
　　--------------
　　TABLE Access BY INDEX ROWID OF T1 (C=2)
　　INDEX(RANGE SCAN) OF T1_I1 (C=1)
　　
　　2. select * from t2 where n1 = 45;
　　-- use full table scan
　　
　　EXECUTION PLAN
　　--------------
　　TABLE ACCESS FULL OF T2 (C=15)
　　
　　3. select /*+ index(t2 t2_i1) */ from t2 where n1=45;
　　-- After using hint, Oracle choose index range scan
　　
　　EXECUTION PLAN
　　--------------
　　TABLE ACCESS BY INDEX ROWID OF T2 (C=16)
　　INDEX(RANGE SCAN) OF T2_I1 (C=1)
　　
　　三. 原因:
　　
　　Oracle一般选择最廉价的执行计划.
　　
　　select table_name, level,
　　avg_data_blocks_per_key,
　　avg_leaf_blocks_per_key,
　　clustering_factor
　　from user_indexes;
　　
　　T1 T2
　　Blevel 1 1
　　Data block/key 1 15
　　Leaf block/key 1 1
　　Clustering factor 96 3000
　　
　　Total Cost = avg_data_blocks_per_key + avg_leaf_blocks_per_key
　　
　　（邱承国注：上文公式是说：总成本是每个键值所包含的平均数据块数目与平均“叶”块数目之和）
　　
　　四. 分析
　　
　　对于T1表, 使用了trunc()截断函数将\"rownum - 1\"的值除以15并进行截断以产生N1字段的值,
　　...
　　Trunc(675/15) = 45
　　Trunc(676/15) = 45
　　Trunc(689/15) = 45
　　...
　　
　　实际上，上述值等于45的记录将一个接一个的紧密排列在表中（甚至有可能完全填充在一个数据块中）。
　　
　　对于T2表，使用mod()取余函数将rownum的值除以200取余数，以产生N1字段的值:
　　
　　mod(45,200) = 45
　　mod(245,200) = 45
　　...
　　mod(2845,200) = 45
　　
　　上述值等于45的记录会每隔200条记录分布在表中（有可能隔1、2个数据块才能找到一条记录以上）
　　
　　通过分析，Oracle能够获得表中数据的散列情况的一个完美的描述。因此优化器就可以精确地计算出Oracle在处理我们的查询时需要访问多少数据块，并且，就象上面简单的例子，计算出这个查询成本中的需要访问数据块的数目
　　
　　五. 为什么会是全表扫描?
　　
　　现在我们看到了，同样的查询，T2表“基于索引扫描”的成本要比在T1表上的高得多，但是为什么Oracle会切换成“全表扫描方式”呢?
　　
　　让我们看看Oracle得两个愚蠢、非常不合适、估计（邱承国注：意译）的做法。
　　
　　首先，每一次块采集都带来一次物理磁盘读操作，其次是一次读入多个块要比读入一个块速度要快得多。

　　
　　那么，这种傲慢的做法导致了什么结果?
　　
　　假如你使用下列SQL语句查询user_tables视图:
　　
　　select table_name,blocks
　　from user_tables;
　　
　　你会发现我们前面提到的两张表每张表使用了96个块.
　　
　　在这片文章开始时，我说明了这个测试用例使用Oracle 8的版本，并且db_file_multiblock_read_count参数取值为8（邱承国注：该参数决定读取数据文件时每次读入多少个块）.
　　
　　大致地说, Oracle会判定出要读取一个96块的表需要96/8 = 12次磁盘读取请求。
　　
　　而由于通过索引访问这张表需要读取16个块或者说是16次磁盘读取请求，从Oracle悲哀的、迷惑的观点出发，很明显全表扫描快得多— 要知道12次要比16少多了。
　　
　　瞧! 假如你需要的数据比较对称地散列在整张表中，你将不得不以“全表扫描”的方式捡索仅占很小百分比的数据，——正如例子中数据块较大而记录数较少因此问题就很明显了。
　　
　　六. 修正
　　
　　事实上, 你可能已经注重到了我计算出的读取次数时12次，同时execution plan报告的成本是15。说起来比较简单：全表扫描的成本（或者说是基于索引的扫描成本）就是
　　
　　\'number of blocks\' /db_file_multiblock_read_count
　　（邱承国注：可以理解为，数据块的数目/读取数据文件时每次读取的最多块数）
　　
　　其实，Oracle使用一个“可调整的”多块读取值来参与计算(尽管当扫描开始时它使用现实请求的尺寸).
　　
　　例如，下列表格时一些现实尺寸和调整值的对比。
　　
　　Actual Adjusted
　　4 4.175
　　8 6.589
　　16 10.398
　　32 16.409
　　64 25.895
　　128 40.865
　　
　　就像你看到的，Oracle还是尝试防止这种因为提供了一个较大的BLOCK数据块读取参数而产生的错误。
　　
　　顺便说一下，在Orace 9 的版本中有一个较小的改变，就是全表扫描的成本有了一些增加调整，在成本计算结果上增加了一个单位——这也就是说V9版本的Oracle其全表扫描回避V8昂贵一些，这样“基于索引的扫描”使用的概率会高一些了。
　　
　　(我打算在 9i和 10G上在进行测试比较)
　　
　　七. 调整
　　
　　我们已经看到了有两个估计被构造进了优化器里，而这是非常不明智的。
　　
　　单单读取一个块和一次夺取多个块成本竟然是一样的——（最不希望看到的，当数据库是运行在文件系统而不是裸设备时更为明显）。
　　
　　读取一个块竟然导致一次物理磁盘的读取？——（那么，buffer cache 用来干什么?)
　　
　　在使用Oracle 8.1的早些时候, 有一对参数答应我们通过一个适当的、符合实际的方式来修正这些估计值。
　　
　　看看 Tim Gorman的文章，准确而又简要的描述了着两个参数：
　　
　　参数Optimizer_index_cost_adj: 缺省为100，可以定义为1到10000。这个参数卓有成效地描述了单单读取一个数据块将比一次读取多个块便宜多少。举例来说，我们通常将OLTP系统的值设为30，它告诉ORACLE单单读取一个块的成本时读取多个块的成本的30％，Oracle将因为这个参数值较低而倾向于使用基于索引的扫描方式。
　　
　　参数Optimizer_index_caching:缺省为0，可以定义为0到100。这个参数告诉Oracle在buffer cache中估计能找到百分之多少地索引块。在这个例子中，假如把这个值设定为接近100，Oracle更愿意使用索引扫描而不是全表扫描。
　　
　　假如把这两个参数都设定成符合实际的值，那真是一件大好事！
　　
　　在参数列表的\"buffer cache hit ratio.\"中设置optimizer_index_caching的值(你不得不自己决定是否这样做，因为它会影响POOL的外形，default pool, keep pool, 或者二者都有)。.
　　
　　optimizer_index_cost_adj参数有一些复杂。在视图v$system_event中查找事件\"db file scattered read\" (multi block reads多块读取)和事件\"db file sequential reads\" (single block reads单块读取)的典型等待时间。将后者的值除以前者再乘以100。
　　
　　八. 优化
　　
　　不要忘了这两个参数在天天、每周的不同时刻都需要随着最终用户的作业情况进行调整。你绝对不能只定义一次而永远使用。
　　
　　幸运的是，在Oracle 9中，这些事情被改进了、现在你可以手机系统的统计信息，通常包括一下四个方面：
　　
　　单个块读取的平均时间
　　多块读取的平均时间
　　当前多块读取的平均时间
　　概念上的可用CPU速度
　　
　　够了，关于这些特性的说明足够写一篇文章了——但还是要记住前面三个使Oracle发现多块读取的真实成本。并且，事实上，CPU速度将使Oracle计算出在不对称地访问那些部件时的CPU成本，例如在一个块中读取每一条记录以便找到一个精确的数据把它展现出来。
　　
　　当你升级到Oracle 9时，首先你应该调查系统统计信息是否被正确地使用了。这项独一无二的特性可以减少你花在调整（\'tune\'）那些笨拙的SQL上面的时间。
　　
　　接下来，不管系统统计信息有多么出色的表现，优化器的参数都必须进行调整——尽管从V8到V9这些参数的精确公式看起来已经发生了变化。
　　
　　九. 不同场景的变化
　　
　　当然了，我列举了一个非常非凡的例子——非唯一性索引的单个字段而且表中没有NUKK值——处理很简单。
(我甚至还没有提起索引的blevel和clustering_factor之间的关联性。在Oracle的日常工作中，还有无数的不同的策略应用在一些案例中。
　　
　　考虑以下的一些案例。我故意把它们忽略了：
　　
　　多字段的索引
　　只使用部分字段的多字段的索引
　　Range scans某一范围内的扫描
　　唯一索引
　　使用非唯一索引描述唯一约束
　　跳过索引的扫描
　　仅用于查询的索引
　　位图索引
　　Null值的影响
　　
　　还有很多没有列出来、这里并没有一个简单的公式能够告诉你Oracle如何计算执行成本——而只有一个大概的方针可以让你尝尝滋味，知道在不同的案例中应该采用的步骤和不同的公式。
　　
　　不过话说回来, 这篇文章的目的在于让你明白一般的步骤，明白在优化器的处理中有两个假定。同时我希望这篇文章能够引导你走完一段漫长的学习道路，理解Oracle优化器的那些看起来很希奇的现象。