18 索引扫描成本计算--优化主题系列

什么时候走索引扫描什么时候走全表扫描今天来彻底搞懂

 

索引扫描成本计算(请下去自己计算索引扫描的成本)

select * from v$version where rownum<2;  --11.2.0.1.0

create table t as select * from dba_objects; 

create index idx_t on t(object_id); 

BEGIN

 DBMS_STATS.GATHER_TABLE_STATS(ownname         => 'TEST',

                               tabname          => 'T',

                               estimate_percent => 100,

                               method_opt       => 'for all columns sizeauto',

                               degree           =>DBMS_STATS.AUTO_DEGREE,

                               cascade          => TRUE);

END;

/

 

selectleaf_blocks,blevel,clustering_factor from dba_indexes where index_name='IDX_T';

select b.num_rows,

       a.num_distinct,

       a.num_nulls,

      utl_raw.cast_to_number(high_value) high_value,

       utl_raw.cast_to_number(low_value)low_value,

       (b.num_rows - a.num_nulls)"NUM_ROWS-NUM_NULLS",

      utl_raw.cast_to_number(high_value) -

      utl_raw.cast_to_number(low_value) "HIGH_VALUE-LOW_VALUE"

  from dba_tab_col_statistics a, dba_tables b

 where a.owner = b.owner

   and a.table_name = b.table_name

   and a.owner = 'TEST'

   and a.table_name = upper('T')

   and a.column_name = 'OBJECT_ID';

先将优化器设置为9i

alter session set optimizer_features_enable='9.2.0'; 

select owner from t where object_id<1000;

索引扫描公式如下:

COST =

blevel +  
 celiling(leaf_blocks *effective index selectivity) +  
 celiling(clustering_factor * effective table selectivity)

 

现在是不是只差索引选择性？？

前面提到聚簇因子越大索引范围扫描就越耗费时间

是不是聚簇因子越大COST就越高？？

是不是聚簇因子大了有时候就不走索引了？？

Blevel 一般是1或者2

影响最大的是选择性

select owner from t where object_id<1000;

我们现在要来算这个选择性

 

这里有个概念叫有效选择性，<的有效选择性为

(limit-low_value)/(high_value-low_value)

所以有效选择性等于(1000-2)/74662

Oracle预估的基数等于有效选择性*(num_rows-num_nulls)=ceil(1000-2)/74662*72469

 

select ceil((1000-2)/74662*72469) from dual;  --969

你们算出来的是不是等于你执行计划上的COST值？？

Oracle预估的基数等于有效性*(num_rows-num_nulls)

执行计划的ROWS 就是这么算出来的

 

Oracle计算的成本等于：

select 1+ceil(161*(1000-2)/74662)+ceil(1636*(1000-2)/74662) fromdual;  --26

我们手工计算的成本等于26，与执行计划看到的吻合。

我们手工计算Oracle返回969行，与执行计划吻合。

 

索引扫描的COST单位是什么？？是不是扫描的块的个数？？

索引扫描是不是单块读最终索引扫描的成本是不是 I/O的次数？？

 

现在能不能回答我什么时候走全表扫描更优化？？什么时候走索引扫描更优化？？

 

是不是最终还是看I/O扫描次数对不对？？

索引说 SQL优化最终的目的是什么？？

这堂课的目的

1. 知道怎么计算COST

2. 知道为什么不要去看COST

3. 知道SQL优化的核心思想为什么是减少I/O次数