oracle 索引的统计信息 二 聚簇因子

    oracle数据库中，聚簇因子是按照索引键值排序的索引行和存储于对应表中数据行的存储顺序的相似程度。oracle是按照如下的算法来计算聚簇因子的值的。

（1）聚簇因子的初始值为1.
（2） oracle首先定位到目标索引处于最左边的叶子块。
（3）从最左边的叶子块的第一个索引键值所在的索引行开始顺序扫描，在顺序扫描的过程中，oracle会比对当前索引行的rowid和它之前的那个索引行（它们是相邻关系）的rowid，
如果这两个rowid并不是指向同一个表块，那么oracle就将聚簇因子的当前值递增为1，如果指向同一个表块，值不变。对比rowid的时候并不需要回表访问相应的表块。
（4）上述比对rowid的过程会一直持续下去，知道顺序扫描完目标索引所有叶子块里的所有索引行。
（5）上述顺序扫描操作完成后，聚簇因子的当前值就是索引统计信息中的clustering_factor,oracle会将其存储在数据字典里。

  

上面这种情况，可以用以下例子来模仿：

点击(此处)折叠或打开

1. create table t1(id number,name char(1200));
   
2. 
   
3. 
   
4. insert into t1 values(1,'1');
   
5. insert into t1 values(3,'3');
   
6. insert into t1 values(5,'5');
   
7. insert into t1 values(7,'7');
   
8. insert into t1 values(9,'9');
   
9. insert into t1 values(11,'11');
   
10. insert into t1 values(13,'13');
    
11. insert into t1 values(15,'15');
    
12. insert into t1 values(17,'17');
    
13. insert into t1 values(19,'19');
    
14. insert into t1 values(2,'2');
    
15. insert into t1 values(4,'4');
    
16. insert into t1 values(6,'6');
    
17. insert into t1 values(8,'8');
    
18. insert into t1 values(10,'10');
    
19. insert into t1 values(12,'12');
    
20. insert into t1 values(14,'14');
    
21. insert into t1 values(16,'16');
    
22. insert into t1 values(18,'18');
    
23. insert into t1 values(20,'20');

1. create index t1_idx on t1(id)
   
   
   

点击(此处)折叠或打开

1. select id,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location from t1
   
2. order by location,id

ID    LOCATION
1    9_2436
3    9_2436
5    9_2436
7    9_2436
9    9_2436
11    9_2437
13    9_2437
15    9_2437
17    9_2437
19    9_2437
2    9_2438
4    9_2438
6    9_2438
8    9_2438
10    9_2438
12    9_2439
14    9_2439
16    9_2439
18    9_2439
20    9_2439

点击(此处)折叠或打开

1. BEGIN
   
2.      DBMS_STATS.GATHER_TABLE_STATS(ownname => 'MEPF_DEV',tabname => 'T1',method_opt => 'for all columns size auto',estimate_percent => 100,cascade => true);
   
3. end;

这个例子，想临索引键值的rowid指向的都是不同的块，聚簇因子为20，表的块数为4，是它的5倍，这个属于极端的情况。
从上述聚簇因子的算法可以知道，如果聚簇因子的值接近对应表的表块的数量，则说明索引存储顺数和对应表中数据行的存储顺序相似程度非常高，这也就意味着oracle走索引范围
扫描后取得目标rowid再去防伪对应表块的数据时，相邻的索引行所对应的rowid即有可能处于同一个表中，即oracle在通过索引行记录rowid回表第一次去读取对应的表块并讲该表
块缓存在buffer cache中后，相邻的索引行回表读取对应的表块时，不需要产生物理io了，因为是同一个块，已经缓存在buffer cache中.如果聚簇因子的值接近表的记录数，相邻的索引行所对应的rowid极有可能不再同一表块中，即oracle在通过索引记录的rowid回表第一次去读取对应的表块并将表块缓存在buffer cache中后，当再通过相邻索引行记录的rowid回表第二次去读取对应的表块中，还需要产生物理I/O.

    结论：聚簇因子高的索引走索引范围扫描时比相同条件下聚簇因为低的索引要耗费更多的物理I/O,所以聚簇因子高的索引走索引范围扫描的成本会比相同条件下聚簇因子低的索引走索引
范围扫描的成本高。
    


在oracle数据库中，能够降低目标索引聚簇因子的唯一方法是：对表中数据按照目标索引的键值排序后重新存储。例子如下：

点击(此处)折叠或打开

1. create table t2 as select * from t1 order by id;
   
2. create index idx_t2 on t2(id);
   
3.  select id,dbms_rowid.rowid_relative_fno(rowid)||'_'||dbms_rowid.rowid_block_number(rowid) location from t2
   
4. order by location,id

ID    LOCATION
1    9_2451
2    9_2451
3    9_2451
4    9_2451
5    9_2451
6    9_2452
7    9_2452
8    9_2452
9    9_2452
10    9_2452
11    9_2453
12    9_2453
13    9_2453
14    9_2453
15    9_2453
16    9_2454
17    9_2454
18    9_2454
19    9_2454
20    9_2454

点击(此处)折叠或打开

1. BEGIN
   
2.      DBMS_STATS.GATHER_TABLE_STATS(ownname => 'MEPF_DEV',tabname => 'T2',method_opt => 'for all columns size auto',estimate_percent => 100,cascade => true);
   
3. end;

现在索引的IDX_T2的聚簇因子的值为4.


    在oracle数据库里，CBO在计算索引范围扫描（Index Range Scan）的成本时会使用如下公式：

IRS Cost=I/O Cost + CPU Cost

    其中CPU cost是否启用以及启用后如何计算，将在5.9 系统统计信息中详细介绍。

上述公式中I/O cost的计算公式为：
    I/O COST=INDEX ACCESS I/O COST + TABLE ACCESS I/O COST
   INDEX ACCESS I/O COST =  BLEVEL + CEIL(#LEAF_BLOCKS*IX_SEL)
    TABLE ACCESS I/O COST = CEIL(CLUSTERING_FACTOR*IX_SEL_WITH_FILTERS)
大概判断出索引范围扫描的成本和聚簇因子成正比。因此，聚簇因子值大小实际上对cbo判断是否走相关的索引起着至关重要的作用。

下面演示一个修改聚簇因子值让原本走索引范围扫描的执行计划变成了走全表扫描的例子。

点击(此处)折叠或打开

1. create table t1 as select * from dba_objects;
   
2. 
   
3. create index inx_t1 on t1(object_id)
   
4. 
   
5. 
   
6. select /*+ cluster_factor_example_1 */ object_id,object_name from t1 where object_id between 103 and 108

如果走全表cost为276。




此时的cluster factor为1111，只需增大此值，让cbo计算出的走索引的成本超过全表扫描的成本，就会走全表扫描。

点击(此处)折叠或打开

1. begin
   
2.       dbms_stats.set_index_stats(ownname => 'MEPF_DEV',INDNAME =>'INX_T1',clstfct => 1000000,no_invalidate => FALSE);
   
3. end;

此时，修改cluster factor为1000000，此时的cost为

继续修改，把cluster factor修改为6000000

点击(此处)折叠或打开

1. begin
   
2.       dbms_stats.set_index_stats(ownname => 'MEPF_DEV',INDNAME =>'INX_T1',clstfct => 6000000,no_invalidate => FALSE);
   
3. end;

此时走了全表扫描，因为cbo计算出的走索引的代价太高，就走了全表扫。
而且可以从实验数据可以看到，修改索引聚簇因子，只是增加了回表的cost。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30109892/viewspace-1826310/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30109892/viewspace-1826310/