B-Tree Index 成本計算

  如果執行計劃採用索引，其成本計算公式與tablescan成本計算公式是不同的，具體實驗如下：

1、建立測試表

SQL>execute dbms_random.seed(0)

SQL>create table t1 as
    select
       trunc(dbms_random.value(0,25)) n1,
       rpad('x', 40) ind_pad,
       trunc(dbms_random.value(0,20)) n2,
       lpad(rownum, 10, '0')  small_vc,
       rpad('x',200) padding
    from
       all_objects
    where rownum<=10000;

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

2、統計分析

SQL>begin
      dbms_stats.gather_table_stats (
        user,
        't1',
        cascade => true,
        estimate_percent => null,
        method_opt => 'for all columns size 1'
        );
     end;

3、生成跟蹤檔案

SQL>alter session set events '10053 trace name context forever, level 2';

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

SQL>alter session set events '10053 trace name context off';

提取關鍵指標值：

BLEVEL=LVLS: 2
LEAF_BLOCKS=#LB=1111
effective index selectivity= ix_sel: 0.002 = (1/#DK: 500)
clustering_factor=CLUF: 9745.00
effective table selectivity= ix_sel_with_filters: 0.002
                           =(1/N1.density:0.04)*(1/IND_PAD.density:1)*(1/N2.density:0.05)


或檢視檢視：

SQL>select table_name, blocks, num_rows
    from user_tables
    where table_name='T1';
 TABLE_NAME                         BLOCKS   NUM_ROWS
------------------------------ ---------- ----------
T1                                    387      10000

SQL>select num_rows, distinct_keys,
           blevel, leaf_blocks, clustering_factor,
           avg_leaf_blocks_per_key, avg_data_blocks_per_key
     from user_indexes
     where table_name='T1'
     and index_name='T1_I1';

NUM_ROWS DISTINCT_KEYS     BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY
---------- ------------- ---------- ----------- ----------------- ----------------------- -----------------------
     10000       500          2        1111              9745                       2                      19       

SQL>select column_name,
           num_nulls, num_distinct, density
    from user_tab_columns
    where table_name='T1'
    and column_name in ('N1', 'N2', 'IND_PAD')
    order by column_name;
COLUMN_NAME                     NUM_NULLS NUM_DISTINCT    DENSITY
------------------------------ ---------- ------------ ----------
IND_PAD                                 0            1          1
N1                                      0           25        .04
N2                                      0           20        .05

4、手工成本計算值與實際成本計算值比較

cost = blevel +
       ceiling(leaf_blocks * effective index selectivity) +
       ceiling(clustering_factor * effective table selectivity)
     = 2 +
       ceiling(1111*0.002) +
       ceiling(9745*0.002)
     =2+3+20=25

實際執行計劃如下：
-----------------------------------------------+-----------------------------------+
| Id  | Operation                    | Name    | Rows  | Bytes | Cost  | Time      |
-----------------------------------------------+-----------------------------------+
| 0   | SELECT STATEMENT             |         |       |       |    25 |           |
| 1   |  TABLE ACCESS BY INDEX ROWID | T1      |    20 |  1160 |    25 |  00:00:01 |
| 2   |   INDEX RANGE SCAN           | T1_I1   |    20 |       |     5 |  00:00:01 |
-----------------------------------------------+-----------------------------------+

結論：兩者值完全相同

備註：實驗依據和公式來源於jonathan lewis  《CBO基礎》

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/354732/viewspace-613607/，如需轉載，請註明出處，否則將追究法律責任。