oracle loop index,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