AVG_LEAF_BLOCKS_PER_key 和 AVG_DATA_BLOCKS_PER_KEY 说明

按照官方文档的说明
AVG_LEAF_BLOCKS_PER_KEY:
Average number of leaf blocks in which each distinct 
value in the index appears, rounded to the nearest 
integer. For indexes that enforce UNIQUEand PRIMARY 
KEYconstraints, this value is always 1
每一个不同的键值平均页块的数量,如果是唯一和主键那么这个
值恒等于1。


AVG_DATA_BLOCKS_PER_KEY:
Average number of data blocks in the table that are 
pointed to by a distinct value in the index rounded to 
the nearest integer. This statistic is the average number 
of data blocks that contain rows that contain a given 
value for the indexed columns
每一个不同键值的对应到表中的块的数量，这个值是包含了
键值行的表的平均的数据块数量


我们按照这个意思进行一次极端的测试
我建立的表中包含5个字段
create table testt1
(name varchar2(10),
 id number(10),
 frist_name varchar2(30),
 t_stat     varchar2(30),
 t_content  varchar2(100));




 
我将在NAME列建立索引和ID列建立索引，name这个列上只有一个唯一的值'test',而
ID列值是唯一的，有100W行记录。
先插入100W行数据。
 declare  
   i number(10);
begin 
  for i in 1..1000000
  loop
  insert into testt1
   values('test',i,'testtest','testtest','testtest');
  end loop;
end;


create index testt1_i on testt1(name);
create index testt1_i2 on testt1(id);


进行统计数据收集


execute dbms_stats.gather_table_stats(ownname => 'SYSTEM',tabname => 'TESTT1',estimate_percent => 70,cascade => true);
这个时候我们来看这两个索引的这两个值
INDEX_NAME                     LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
------------------------------ ----------- ------------- ----------------------- ----------------------- -----------------
TESTT1_I                              2263             1                    2263                    6557              6557
TESTT1_I2                             2294        999744                       1                       1              6580


可以明显看到区别，基本上的TESTT1_I的AVG_LEAF_BLOCKS_PER_KEY=索引的页节点的块数量，AVG_DATA_BLOCKS_PER_KEY=表的块数量
TESTT1_I2上AVG_LEAF_BLOCKS_PER_KEY=1表示每访问一个键值只需要访问一个页块，而AVG_DATA_BLOCKS_PER_KEY=1，说明这个键值
存在于一个数据块中。
可以想象到的，如果AVG_LEAF_BLOCKS_PER_KEY越大那么可以说明表中索引键值的唯一值越少，如果AVG_DATA_BLOCKS_PER_KEY越大则说明
访问一个唯一值得时候需要的访问的表的块数量就越多，那么这两个值对于索引的利用来讲当然是越小越倾向于使用索引。


最后我们来测试一次表中数据不是那么有序的
 declare  
   i number(10);
   m number(10);
begin 
  for m in 1..2000
  loop
  for i in 1..500
  loop
  insert into testt1
   values('test',i,'testtest','testtest','testtest');
  end loop;
  end loop;
end;


可以看到我的ID列数据将会
1-500这样的不断重复。这样我们的数据就不是有序的，而索引却是有序的。那么这个时候我们可能
AVG_LEAF_BLOCKS_PER_KEY很小，但是AVG_DATA_BLOCKS_PER_KEY比较大，同时CLUSTERING_FACTOR也会比较大
因为索引中的数据会如下排列
1 1 1 1（2000个1），2 2 2 2 （2000个2）等等
但是表中则是
1 2 3 4 到500，然后在是1 2 3 4 到500。
INDEX_NAME                     DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY CLUSTERING_FACTOR
------------------------------ ------------- ----------------------- ----------------------- -----------------
TESTT1_I2                                500                       6                    2001           1000880


注意这里的CLUSTERING_FACTOR基本是我的行数，这说明每访问一个键值，需要跳跃的表的块数量基本是500次，也就是说
表中的记录看起来是杂乱无章的。