一条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

　创建索引

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

转载于:https://www.cnblogs.com/fjzsl/p/8551985.html