如果执行计划采用索引,其成本计算公式与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基础》
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/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/354732/viewspace-613607/