聚簇因子笔记

1、聚簇因子（Clustering factor)

     索引行的存储顺序与表中行的存储顺序之间的相似程度。

 

     当相似度高时，这些数据行就会密集地存储在相对较少的数据块中，这是聚簇因子比较好的情况。Oracle中，对于同一个查询语句，有时候会很快的完成，有时候却很慢，但是表结构什么的完全一致，表中的数据也完全一致，这个具体是什么原因呢，就要从Index中的细节说起了。在Oracle中的一个特殊的视图user_indexes中有一个特殊的列，名字是clustering_factor，这个值的内容就是如果访问表的整个表数据，会造成多少次数据库IO。

     A：如果这个值与块数接近，则说明表相当有序，得到了很好的组织。在这种情况下，同一个叶子块中的索引条目可能指向同一个数据块中的行。(clustering_factor=blocks 好)

     B：如果这个值与行数接近，表的次序可能就是非常随机的。在这种情况下，同一个叶子块上的索引条目不太可能指向同一个数据块上的行。(clustering_factor=num_rows 不好)

 

     可以把聚簇因子看作是通过索引读取整个表时对表执行的逻辑I/O次数。也就是说聚簇因子指示了表相对于索引本身的有序程度。当oracle对索引结构执行区间扫描时，如果它发现索引中的下一行与前一行在同一个数据块上，就不会再执行另一个I/O从缓冲区缓存中获得表块。它已经有了表块的一个句柄，只需要直接使用就行了。不过，如果下一行不在同一个块上，就会释放当前的这个块，而执行另一个物理I/O在缓冲区缓存存放要处理的下一个块。

     另外还要记住：对于一个表来说，一般只有一个索引能够有合适的聚簇因子！表中的行可能只以一种方式排序。

我们可以通过下面的SQL语句来查看。

 

select T.TABLE_NAME || '.' || I.index_name IDX_NAME,
       I.clustering_factor,
       T.BLOCKS,
       T.NUM_ROWS
  from user_indexes i, user_tables t
 where i.table_name = t.TABLE_NAME
   and t.TABLE_NAME = ?
 ORDER BY T.TABLE_NAME, I.table_NAME;

 

       在这个SQL语句中，?代表的就是我们要检索的Index的名称。在表中数据有时候属于无序状态，这个时候的CLUSTERING_FACTOR比较接近NUM_ROWS，说明如果扫描整个表，每次都要根据Index来读取相应行的RowID，这个时候的IO操作很多，自然检索时间会比较长。如果数据有序的话，CLUSTERING_FACTOR比较接近BLOCKS，说明相邻的数据在一个块中，减少了IO操作数量，自然检索时间会大大降低。

 

2、准备实验条件

CREATE TABLE test_1 AS
  SELECT ROWNUM rn, a.* FROM all_objects a ORDER BY object_name DESC
;
CREATE INDEX ind_test_1 ON test_1(rn);

CREATE TABLE test_2 AS
 SELECT * FROM (SELECT ROWNUM rn, a.* FROM all_objects a) ORDER BY rn ASC
;
CREATE INDEX ind_test_2 ON test_2(rn); 

 

SQL> EXEC DBMS_STATS.gather_table_stats('USERS','TEST_1');
 
SQL> EXEC DBMS_STATS.gather_table_stats('USERS','TEST_2');
 
SQL> EXEC DBMS_STATS.gather_index_stats('USERS', 'IND_TEST_1');

SQL> EXEC DBMS_STATS.gather_index_stats('USERS', 'IND_TEST_2');

 

3、执行查询操作

SELECT * FROM tEST_1 WHERE rn BETWEEN 10000 AND 10200;

 统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         85  consistent gets
          0  physical reads
          0  redo size
      11897  bytes sent via SQL*Net to client
        543  bytes received via SQL*Net from client
         15  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        201  rows processed

 

SELECT * FROM tEST_2 WHERE rn BETWEEN 10000 AND 10200;

 

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         35  consistent gets
          0  physical reads
          0  redo size
      11897  bytes sent via SQL*Net to client
        543  bytes received via SQL*Net from client
         15  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
        201  rows processed

 

4、观察试验结果

     通过执行统计信息观察，test_1表的查询一致读是85，而test_2表的一致读只有35，竟然test_1的一致读尽然是test_2的2倍还多，是不是有点奇怪，同样的表结构，同样的数据（test_2多两条数据）

 

 5、分析原因

      

select T.TABLE_NAME || '.' || I.index_name IDX_NAME,
       I.clustering_factor,
       T.BLOCKS,
       T.NUM_ROWS
  from user_indexes i, user_tables t
 where i.table_name = t.TABLE_NAME
   and t.TABLE_NAME IN( 'TEST_1','TEST_2')
 ORDER BY T.TABLE_NAME, I.table_NAME;

 

 

IDX_NAME	CLUSTERING_FACTOR	BLOCKS	NUM_ROWS
TEST_1.IND_TEST_1	26358	747	50490
TEST_2.IND_TEST_2	727	747	50492