创建表
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
创建索引
create index tl_il on tl(n1,ind_pad,n2) nologging pctfree 91;
统计表
begin
dbms_stats.gather_table_stats(ownname => 'TEST',
tabname =>'Tl');
end;
查看统计表之后 的效果
select column_name, num_distinct from user_tab_columns where table_name = 'TL' and column_name in ('N1', 'N2', 'IND_PAD') order by column_name
查看用户索引
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 = 'TL' and index_name = 'TL_IL'
计算成本:
select small_vc
from tl
where n1 = 2
and n2 = 3
第一步成本 = blevel+ ceil((n1.selectivity*n2.selectivity)*blocks)=2+3=5
n1.selectivity=1/25
n2.selectivity=1/20
blocks=1111
第二步成本 = ceil(n1.selectivity*n2.selectivity*clustering_factor)=(0.002*9772)=20
n1.selectivity=1/25
n2.selectivity=1/20
clustering_factor=9772
总共的成本=第一步成本+第二步成本=5+20=25