（2）简单B树访问——计算执行计划的成本

通过索引来访问表的成本公式应该包含3个与块相关的组件：按降序遍历的分支层数、遍历 叶块的数目和访问过的表块的数目。

1、入门

SQL> create table t1 
  2  nologging
  3  as
  4  select
  5  trunc(dbms_random.value(0,25))n1,   --n1会产生25个不同的值。
  6  rpad('x',40)ind_pad,                --只有一个值。
  7  trunc(dbms_random.value(0,20))n2,   --n2会产生20个不同的值。
  8  lpad(rownum,10,'0')small_vc,
  9  rpad('x',200)padding
 10  from
 11  all_objects
 12  where
rownum  <= 10000
 14  ;

表已创建。

SQL> create index t1_i1 on t1(n1, ind_pad, n2) 
  2  nologging
  3  pctfree 91
  4  ;

索引已创建。

SQL> begin
  2  dbms_stats.gather_table_stats(
  3  user,
  4  't1',
  5  cascade => true,
  6  estimate_percent => null,
  7  method_opt => 'for all columns size 1'
  8  );
  9  end;
 10  /

PL/SQL 过程已成功完成。

SQL> select t.TABLE_NAME, t.NUM_ROWS, t.BLOCKS
  2    from user_tables t
  3   where table_name = 'T1';

TABLE_NAME                       NUM_ROWS     BLOCKS
------------------------------ ---------- ----------
T1                                  10000        387

SQL> select s.table_name,
  2         s.column_name,
  3         s.num_distinct,
  4         s.density,
  5         s.num_nulls,
  6         s.avg_col_len
  7    from user_tab_col_statistics s
  8   where table_name = 'T1';

TABLE_NAME                     COLUMN_NAME                    NUM_DISTINCT    DENSITY  NUM_NULLS AVG_COL_LEN
------------------------------ ------------------------------ ------------ ---------- ---------- -----------
T1                             N1                                       25        .04          0           3
T1                             IND_PAD                                   1          1          0          41
T1                             N2                                       20        .05          0           3
T1                             SMALL_VC                              10000      .0001          0          11
T1                             PADDING                                   1          1          0         201

SQL> select i.index_name,
  2         i.table_name,
  3         i.blevel,
  4         i.leaf_blocks,
  5         i.distinct_keys,
  6         i.clustering_factor,
  7         i.num_rows
  8    from user_indexes i
  9   where index_name = 'T1_I1';

INDEX_NAME                     TABLE_NAME                         BLEVEL LEAF_BLOCKS DISTINCT_KEYS CLUSTERING_FACTOR   NUM_ROWS
------------------------------ ------------------------------ ---------- ----------- ------------- ----------------- ----------
T1_I1                          T1                                      2        1111           500              9752      10000

可以看见DISTINCT_KEYS等于索引列的NUM_DISTINCT相乘 = 25 * 1 * 20 = 500。一共有500种组合，没种组合对应的行数就是20行（10000 / 500 = 20）。

SQL> select small_vc
  2    from t1
  3   where n1 = 2
  4     and ind_pad = rpad('x', 40)
  5     and n2 = 3;

执行计划
----------------------------------------------------------
Plan hash value: 1429545322

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    20 |  1160 |    25   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    20 |  1160 |    25   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |    20 |       |     5   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("N1"=2 AND "IND_PAD"='x
              ' AND "N2"=3)

索引选择率：n1选择率*ind_pad选择率*n2选择率=1/25 * 1 * 1/20 = 1/500 = 1 / DISTINCT_KEYS

返回基数：1 / DISTINCT_KEYS * NUM_ROWS = 1/500 * 10000 = 20。

通过索引查询的成本为25，基数为20。基数估算非常准确，但成本又是如何得出的呐？CBO认为一共读取了20行数据，对访问表的成本为20不应该感到惊奇。索引的成本是5，可以将其中两个归咎于索引的blevel等于2。此处还剩余3个成本——可能是优化器已经估计到为了获取所必须的20个不同的行，必须遍历3个叶块。

effective index selectivity：将独立列的选择率相乘来计算联合选择率的方式是一种通用解决方案。但是此处有一个改进是我们评估索引时必须考虑的。假定有一个查询还包含了额外的谓词small_vc='000001'。如果我们选择通过先有的索引来访问表，那么一直到接触到了表之后，都不能用上最后这个谓词——因此，这个谓词将无法影响我们将要访问的数据的分数，只能影响最终返回的数据的分数。
effective table selectivity：当我们计算使用索引的成本时，有效表选择率应该仅仅是基于那些在接触到表之前就能在索引中进行评估的谓词。在这个示例中，针对表的所有谓词都能够在索引中找到，因此，可以直接认为有效表选择率也是0.04*1*0.05=0.002。

clustering_factor：是一个度量标准，用于索引的有序度和表的混乱度之间的比较。从表上面看，优化器安装索引中的顺序来遍历表，并追踪索引项有多少次是从一个表块跳转到另一个表块，从而计算clustering_factor。没跳转一次，计数器将会增加一次——计算器最终的值就是clustering_factor的值。清楚了clustering_factor的计算过程之后，我们就应该知道clustering_factor的最小值等于表中的块数目，最大值等于表中的行数——前提是我们已经得到了统计信息。

 cost = blevel + 
       ceiling(leaf_blocks * effective index selectivity) + 
       ceiling(clustering_factor * effective table selectivity)

SQL> select       
  2         2 + 
  3         ceil(1111 * (1/25 * 1 * 1/20)) + 
  4         ceil(9752 * (1/25 * 1 * 1/20))
  5  from dual;

2+CEIL(1111*(1/25*1*1/20))+CEIL(9752*(1/25*1*1/20))
---------------------------------------------------
                                                 25

我们经常可以通过重建索引来减小索引中leaf_blocks参数的大小；但是，重建索引对于clustering_factor参数没有影响。
 

2、如何处理基于区间的测试（比如，n2 between 1 and 3）

SQL> select
  2  /*+ index(t1) */
  3   small_vc
  4    from t1
  5   where n1 = 2
  6     and ind_pad = rpad('x', 40)
  7     and n2 between 1 and 3;

执行计划
----------------------------------------------------------
Plan hash value: 1429545322

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    82 |  4756 |    93   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    82 |  4756 |    93   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |    82 |       |    12   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("N1"=2 AND "IND_PAD"='x
              ' AND "N2">=1 AND "N2"<=3)

n2选择率：(high_limit-low_limit)/(high_value-low_value)+2/num_distinct = ((3-1)/(19-0)+2/20)

effective index selectivity：(1/25 * 1 * ((3-1)/(19-0)+2/20))
effective table selectivity：和前面的示例一样，针对表的所有谓词都能够在索引中找到，因此，可以直接认为有效表选择率等同于有效索引选择率。

SQL> select round((1/25 * 1 * ((3-1)/(19-0)+2/20)) * 10000) from dual;

ROUND((1/25*1*((3-1)/(19-0)+2/20))*10000)
-----------------------------------------
                                       82

cost = blevel + 
     ceiling(leaf_blocks * effective index selectivity) + 
     ceiling(clustering_factor * effective table selectivity);

SQL> select       
  2         2 + 
  3         ceil(1111 * (1/25 * 1 * ((3-1)/(19-0)+2/20))) + 
  4         ceil(9752 * (1/25 * 1 * ((3-1)/(19-0)+2/20)))
  5  from dual;

2+CEIL(1111*(1/25*1*((3-1)/(19-0)+2/20)))+CEIL(9752*(1/25*1*((3-1)/(19-0)+2/20)))
---------------------------------------------------------------------------------
                                                                               93

为了简单起见，上面的测试是针对索引的最后一列基于区间测试的。但是，如果对索引前面的列进行基于区间测试的话，又会出什么样的结果呢？

SQL> alter session set "_optimizer_skip_scan_enabled"=false;

会话已更改。

SQL> set autotrace trace exp;
SQL> select
  2  /*+ index(t1) */
  3   small_vc
  4    from t1
  5   where n1 between 1 and 3
  6     and ind_pad = rpad('x', 40)
  7     and n2 = 2;

执行计划
----------------------------------------------------------
Plan hash value: 1429545322

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |    82 |  4756 |   264   (0)| 00:00:04 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |    82 |  4756 |   264   (0)| 00:00:04 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |    82 |       |   184   (0)| 00:00:03 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("N1">=1 AND "IND_PAD"='x
              ' AND "N2"=2 AND "N1"<=3)
       filter("N2"=2 AND "IND_PAD"='x
              ')

n1选择率：(high_limit-low_limit)/(high_value-low_value)+2/num_distinct = ((3-1)/(24-0)+2/25)

联合选择率：(((3-1)/(24-0)+2/25) * 1 * 1/20)，将其与表中的行数相乘，对得到的乘积进行四舍五入之后，得到最终结果为82，和执行计划基数一样。

当我们对某列能够进行区间扫描或者不能为这样一列提供测试时，针对后面列的谓词的索引叶块的选择将不仅仅限定于必须分析的叶块。有效表选择率将索引列中的所有谓词联合在一起，有效索引选择率可能不得不使用基于索引主导列上的谓词子集。在这种情况下，有效索引选择率基于列n1上的谓词进行计算。由于列n1上的测试是基于区间的，因此，ind_pad和n2上的谓词并不限制需要遍历的索引叶块的数目。最终访问索引的叶行时，需要使用所有这3个谓词来确定是否访问表，因此，有效表选择率仍然包含了3个列上的谓词。有效索引选择率就是n1列的选择率。

effective index selectivity：((3-1)/(24-0)+2/25)

effective table selectivity：(((3-1)/(24-0)+2/25) * 1 * 1/20)

SQL> select round((((3-1)/(24-0)+2/25) * 1 * 1/20) * 10000) from dual;

ROUND((((3-1)/(24-0)+2/25)*1*1/20)*10000)
-----------------------------------------
                                       82

cost = blevel + 
     ceiling(leaf_blocks * effective index selectivity) + 
     ceiling(clustering_factor * effective table selectivity);

SQL> select       
  2         2 + 
  3         ceil(1111 * ((3-1)/(24-0)+2/25)) + 
  4         ceil(9752 * (((3-1)/(24-0)+2/25) * 1 * 1/20))
  5  from dual;

2+CEIL(1111*((3-1)/(24-0)+2/25))+CEIL(9752*(((3-1)/(24-0)+2/25)*1*1/20))
------------------------------------------------------------------------
                                                                     264

因此，在复合索引中，经常进行基于区间测试的列应该位于经常进行等价测试的列之后。

如何处理多列索引的部分应用

select *
  from t1
 where col1 = {const}
   and col2 = {const}
   and col4 = {const};

我们基于col1和col2来计算有效索引选择率，并且基于col1、col2和col4来计算有效表选择率。

 3、索引全局扫描

 有时候，优化器会认为最优的访问路劲就是安装正确的索引顺序访问整个索引。导致这种行为的一个原因可能是为了避免使用order by子句的排序，另一个原因可能是表中几乎所有的数据都已经被删除，仅基于索引访问几行数据比扫描大量的空表块要快得多。

SQL> alter session set "_optimizer_skip_scan_enabled"=false;

会话已更改。

SQL> select
  2  /*+ index(t1) */
  3   small_vc
  4    from t1
  5   where n2 = 2
  6   order by n1;

执行计划
----------------------------------------------------------
Plan hash value: 3901968637

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |   500 |  8500 |  1602   (1)| 00:00:20 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T1    |   500 |  8500 |  1602   (1)| 00:00:20 |
|*  2 |   INDEX FULL SCAN           | T1_I1 |   500 |       |  1114   (1)| 00:00:14 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("N2"=2)
       filter("N2"=2)

需要注意的是，在上述代码的查询中，order by子句并没有体现出相关作用。在到达叶块之前，我们对索引并没有什么限制（INDEX FULL SCAN），因此，可以认为有效索引选择率的值为1（100%）。当浏览到叶节点时，可以标识出n2=2对应的记录，该谓词对应的选择率为1/20，因此我们可以认为这就是有效表选择率的值。

SQL> set autotrace off;
SQL> select       
  2         2 + 
  3         ceil(1111 * 1) + 
  4         ceil(9752 * 1/20)
  5  from dual;

2+CEIL(1111*1)+CEIL(9752*1/20)
------------------------------
                          1601

该示例误差较小，因此我们无需过分关注误差。

4、仅索引查询

如果查询不对表进行访问，那么可能只要简单的忽略掉公式中的最后一部分就可以了——即忽略掉表访问导致的成本。

SQL> select
  2  /*+ index(t1) */
  3   n2
  4    from t1
  5   where n1 between 6 and 9
  6   order by n1;

执行计划
----------------------------------------------------------
Plan hash value: 3836157954

--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |  2050 | 12300 |   230   (0)| 00:00:03 |
|*  1 |  INDEX RANGE SCAN| T1_I1 |  2050 | 12300 |   230   (0)| 00:00:03 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("N1">=6 AND "N1"<=9)

SQL> select       
  2         2 + 
  3         ceil(1111 * ((9-6)/(24-0)+2/25))
  4  from dual;

2+CEIL(1111*((9-6)/(24-0)+2/25))
--------------------------------
                             230

5、3个选择率

SQL> set autotrace trace exp;
SQL> select
  2  /*+ index(t1) */
  3   small_vc
  4    from t1
  5   where n1 between 1 and 3
  6     and ind_pad = rpad('x', 40)
  7     and n2 = 2
  8     and small_vc = lpad(100, 10);

执行计划
----------------------------------------------------------
Plan hash value: 1429545322

-------------------------------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |    58 |   264   (0)| 00:00:04 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| T1    |     1 |    58 |   264   (0)| 00:00:04 |
|*  2 |   INDEX RANGE SCAN          | T1_I1 |    82 |       |   184   (0)| 00:00:03 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SMALL_VC"='       100')
   2 - access("N1">=1 AND "IND_PAD"='x
              ' AND "N2"=2 AND "N1"<=3)
       filter("N2"=2 AND "IND_PAD"='x
              ')

有效索引选择率：((3-1)/(24-0)+2/25)

有效表选择率：(((3-1)/(24-0)+2/25)*1*1/20)

small_vc选择率：1/10000
最终表选择率：：((((3-1)/(24-0)+2/25)*1*1/20)*1/10000)

SQL> select round(((3-1)/(24-0)+2/25)*10000) from dual;

ROUND(((3-1)/(24-0)+2/25)*10000)
--------------------------------
                            1633  --这个1633是访问索引的“输入基数”，9i、10g就会在INDEX RANGE SCAN的地方显示“输入基数”。

SQL> select round((((3-1)/(24-0)+2/25)*1*1/20)*10000) from dual;

ROUND((((3-1)/(24-0)+2/25)*1*1/20)*10000)
-----------------------------------------
                                       82   --这个82是访问索引的“输出基数”，11g就会在INDEX RANGE SCAN地方显示“输出基数”。

SQL> select ceil(((((3-1)/(24-0)+2/25)*1*1/20)*1/10000)*10000) from dual;

CEIL(((((3-1)/(24-0)+2/25)*1*1/20)*1/10000)*10000)
--------------------------------------------------
                                                 1   --可以看见这个1就是查询返回的行。

SQL> select       
  2         2 + 
  3         ceil(1111 * ((3-1)/(24-0)+2/25)) + 
  4         ceil(9752 * (((3-1)/(24-0)+2/25)*1*1/20))
  5  from dual;

2+CEIL(1111*((3-1)/(24-0)+2/25))+CEIL(9752*(((3-1)/(24-0)+2/25)*1*1/20))
------------------------------------------------------------------------
                                                                     264    --这里是这个查询的成本。