oracle index 聚集因子

简单看一下clustering_factor
简单的说CLUSTERING_FACTOR 用于INDEX 的有序度和表的混乱度之间比较
b*tree index是经过排序的
例如 INDEX中 记录的第一个rowid指向 表所在DATAFILE 中 BLOCK#1 第1行 计数器 记为1，第2个rowid 指向 BLOCK#2 由于改变了块 所以 计数器加1 ，INDEX 第3个rowid
指向BLOCK#2 块没变 所以计数器还为2，接着沿INDEX执行 第4个rowid 指向BLOCK#1 块又变了计数器加1
计数器对应着CLUSTERING_FACTOR 计数器每次从一个块到另一个新块时候加1 这样CLUSTERING_FACTOR也加一
所以clustering_factor可以描述数据在表中的散布方式
如果clustering_factor接近表中的行数，大多行都不在同一个块中,分布太散
当clustering_factor接近表中的块数，说明数据集中有序

当用INDEX 获取一行以上数据时（INDEX RANGE SCAN），需要遍历INDEX的一部分 叫INDEX的 X%，扫描INDEX 时必须逐行的读取表,那么当遍历INDEX 的 X%时，转换表块的次数就等于clustering_factor 的 X%

 

另外clustering_factor对于oracle 优化器计算index cost 有直接关系


cost =
blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)

 

今天读troubleshooting oracle performance 找到一个直接获取clusering_factor的函数脚本特此记录,仔细看可以发现 计算方式与上面的理论是一样的

SQL> show user
USER is "XH"
SQL> create table t3 (a int ,b int);

Table created.

SQL> declare
  2  begin
  3  for i in 1..10000 loop
  4  insert into t3 values(i,i+1);
  5  end loop;
  6  commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> create index t3_ind on t3(a);

Index created.

SQL> select clustering_factor from user_ind_statistics where index_name='T3_IND';

CLUSTERING_FACTOR
-----------------
               18

SQL> CREATE OR REPLACE FUNCTION clustering_factor (
  2    p_owner IN VARCHAR2,
  3    p_table_name IN VARCHAR2,
  4    p_column_name IN VARCHAR2
  5  ) RETURN NUMBER IS
  6    l_cursor             SYS_REFCURSOR;
  7    l_clustering_factor  BINARY_INTEGER := 0;
  8    l_block_nr           BINARY_INTEGER := 0;
  9    l_previous_block_nr  BINARY_INTEGER := 0;
 10    l_file_nr            BINARY_INTEGER := 0;
 11    l_previous_file_nr   BINARY_INTEGER := 0;
 12  BEGIN
 13    OPEN l_cursor FOR
 14      'SELECT dbms_rowid.rowid_block_number(rowid) block_nr, '||
 15      '       dbms_rowid.rowid_to_absolute_fno(rowid, '''||
 16                                               p_owner||''','''||
 17                                               p_table_name||''') file_nr '||
 18      'FROM '||p_owner||'.'||p_table_name||' '||
 19      'WHERE '||p_column_name||' IS NOT NULL '||
 20      'ORDER BY ' || p_column_name;
 21    LOOP
    FETCH l_cursor INTO l_block_nr, l_file_nr;
 22   23      EXIT WHEN l_cursor%NOTFOUND;
 24      IF (l_previous_block_nr <> l_block_nr OR l_previous_file_nr <> l_file_nr)
 25      THEN
 26        l_clustering_factor := l_clustering_factor + 1;
 27      END IF;
 28      l_previous_block_nr := l_block_nr;
 29      l_previous_file_nr := l_file_nr;
 30    END LOOP;
 31    CLOSE l_cursor;
 32    RETURN l_clustering_factor;
 33  END;
 34  /

Function created.

SQL> select clustering_factor('XH','T3','A') from dual;

CLUSTERING_FACTOR('XH','T3','A')
--------------------------------
                              18