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系统的值