本实验基于 Oracle11.2.0.1 Scott 账户。

    1. select * from v$version where rownum=1;
    1. BANNER
    2. ----------------------------------------------------------------------
    3. Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

    我们先创建一个表名为 t_indexscan_cost。

    1. create table t_indexscan_cost as select * from dba_objects;
    1. Table created.

    我们在 object_id 列上建立索引如下。

    1. create index idx_cost on t_indexscan_cost(object_id);
    1. Index created.

    收集表统计信息如下。

    1.  BEGIN
    2.     DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
    3.                                  tabname          => 'T_INDEXSCAN_COST',
    4.                                  estimate_percent => 100,
    5.                                 method_opt        => 'for all columns size 1',
    6.                                  degree           => 1,
    7.                                  cascade          => TRUE);
    8.   END;
    1. PL/SQL procedure successfully completed.

    我们查看表总行数、object_id 最大值、object_id 最小值以及 null 值个数。

    1.  select b.num_rows,
    2.           a.num_distinct,
    3.           a.num_nulls,
    4.          utl_raw.cast_to_number(high_value) high_value,
    5.          utl_raw.cast_to_number(low_value) low_value,
    6.          utl_raw.cast_to_number(high_value) -
    7.          utl_raw.cast_to_number(low_value) "HIGH_VALUE-LOW_VALUE"
    8.     from dba_tab_col_statistics a, dba_tables b
    9.    where a.owner = b.owner
    10.      and a.table_name = b.table_name
    11.      and a.owner = 'SCOTT'
    12.      and a.table_name = ('T_INDEXSCAN_COST')
    13.      and a.column_name = 'OBJECT_ID';
    1.   NUM_ROWS NUM_DISTINCT  NUM_NULLS HIGH_VALUE  LOW_VALUE HIGH_VALUE-LOW_VALUE
    2. ---------- ------------ ---------- ---------- ---------- --------------------
    3.      72645        72645          0      76239          2                76237

    我们查看下面 SQL 语句执行计划。

    1. select owner from t_indexscan_cost where object_id<1000;
    1. 942 rows selected.
    3. Execution Plan
    4. ----------------------------------------------------------
    5. Plan hash value: 1756649757
    7. -----------------------------------------------------------------------------------
    8. | Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)|
    9. -----------------------------------------------------------------------------------
    10. |   0 | SELECT STATEMENT            |                  |   951 | 10461 |    19   (0)|
    11. |   1 |  TABLE ACCESS BY INDEX ROWID| T_INDEXSCAN_COST |   951 | 10461 |    19   (0)|
    12. |*  2 |   INDEX RANGE SCAN          | IDX_COST         |   951 |       |     4   (0)|
    13. -----------------------------------------------------------------------------------
    15. Predicate Information (identified by operation id):
    16. ---------------------------------------------------
    18.    2 - access("OBJECT_ID"<1000)

    执行计划中，T_INDEXSCAN_COST 表走的是索引范围扫描。Cost 为 19。那么这 Cost 是怎么算出来的呢？我们先来看一下索引范围扫描的成本计算公式。

    1. cost =  
    2.  blevel +  
    3.  celiling(leaf_blocks *effective index selectivity) +  
    4.  celiling(clustering_factor * effective table selectivity)

    索引扫描成本计算公式中，blevel、leaf_blocks、clustering_factor 都可以通过下面查询得到。

    1.  select leaf_blocks, blevel, clustering_factor
    2.     from dba_indexes
    3.    where owner = 'SCOTT'
    4.      and index_name = 'IDX_COST';
    1. LEAF_BLOCKS     BLEVEL CLUSTERING_FACTOR
    2. ----------- ---------- -----------------
    3.         161          1              1113

    blevel 表示索引的二元高度，blevel 等于索引高度 −1，leaf_blocks 表示索引的叶子块个数，clustering_factor 表示索引的集群因子，effective index selectivity 表示索引有效选择性，effective table selectivity 表示表的有效选择性。

    索引范围扫描成本计算 - 图1

    那么这里有效选择性 =（1000−2）/（ 76239−2）。

    执行计划中，CBO 估算返回的 Rows 为 951，这 951 是怎么算出来的呢？

    索引范围扫描成本计算 - 图2。

    1. select ceil((1000-2)/(76239-2)*(72645-0)) from dual;
    1. CEIL((1000-2)/(76239-2)*(72645-0))
    2. ----------------------------------
    3.                                951

    现在大家应该理解为什么我们曾在 1.3 节中提出执行计划中的 Rows 都是假的这个观点了。如果 where 条件较多，那么 CBO 在估算 Rows 的时候就会出现较大偏差，而且通常将 Rows 算小。因为当 where 条件变多的时候，CBO 估算返回的 **Rows= 某列选择性*某列选择性*某列选择性*...*表总行数**。选择性一般来说都是小于 1 的分数，当 where 条件变多变复杂之后，CBO 估算的**Rows= 小于 1 的分数*小于 1 的分数*小于 1 的分数*...*表的总行数**，这种情况下 Rows 当然会越算越小（很多时候 Rows 经常被估算为 1）。

    根据上述信息，现在我们来计算索引扫描的成本。

    1. select 1+ceil(161*998/76237)+ceil(1113*998/76237) from dual;
    1. 1+CEIL(161*998/76237)+CEIL(1113*998/76237)
    2. ------------------------------------------
    3.                                         19

    手动计算出来的成本为 19，正好与执行计划中的 Cost 吻合。

    在 1.4 节中我们曾经提到，如果回表次数太多，就不应该索引扫描，而应该走全表扫描。我们也可以从索引扫描的成本公式中验证该理论。clustering_factor * effective table selectivity 表示回表的 Cost，在示例中，回表的 Cost 为 15，回表的 Cost 占据整个索引扫描 Cost 的 79%。这就是回表次数太多不能走索引扫描的原因。

    索引范围扫描成本计算公式的本质含义是什么呢？我们再来看一下索引范围扫描的成本计算公式。

    1. cost =  
    2.  blevel +  
    3.  celiling(leaf_blocks *effective index selectivity) +  
    4.  celiling(clustering_factor * effective table selectivity)

    在 Oracle 数据库中，Btree 索引是树形结构，索引范围扫描需要从根扫描到分支，再扫描到叶子。叶子与叶子之间是双向指向的。blevel 等于索引高度 −1，正好是索引根块到分支块的距离。leaf_blocks *effective index selectivity表示可能需要扫描多少叶子块。clustering_factor * effective table selectivity 表示回表可能需要耗费多少 I/O。

    索引范围扫描是单块读，回表也是单块读，因此，我们得到如下结论：索引扫描成本计算公式其本质就是单块读物理 I/O 次数。

    为什么全表扫描成本计算公式要除以单块读耗时呢？上文提到，全表扫描 COST= 多块读物理 I/O 次数*多块读耗时/单块读耗时，索引范围扫描 COST= 单块读物理 I/O 次数。现在我们对全表扫描 COST 以及索引范围扫描 COST 都乘以单块读耗时：

    全表扫描 COST*单块读耗时 = 多块读物理 I/O 次数*多块读耗时 = 全表扫描总耗时

    索引范围扫描 COST*单块读耗时 = 单块读物理 I/O 次数*单块读耗时 = 索引扫描总耗时

    到此，大家应该明白优化器何时选择全表扫描，何时选择索引扫描，就是比较走全表扫描的总耗时与走索引扫描的总耗时，哪个快就选哪个。