Oracle收集索引统计信息

             相信大家对索引结构非常熟悉了，它是由根、支、叶组成。当然这里指的是常用的B+树索引。我们依然使用《Oracle收集表与列统计信息》里的测试表。下面分析索引统计信息的相关内容。

一、如何查询索引统计信息

             查询索引统计信息需要用到user_ind_statistics，下面是典型的查询语句。

SELECT INDEX_NAME              AS NAME,       BLEVEL,       LEAF_BLOCKS             AS LEAF_BLKS,       DISTINCT_KEYS           AS DST_KEYS,       NUM_ROWS,       CLUSTERING_FACTOR       AS CLUST_FACT,       AVG_LEAF_BLOCKS_PER_KEY AS LEAF_PER_KEY,       AVG_DATA_BLOCKS_PER_KEY AS DATA_PER_KEY  FROM USER_IND_STATISTICS WHERE TABLE_NAME = 'T';  NAME           BLEVEL  LEAF_BLKS   DST_KEYS   NUM_ROWS CLUST_FACT LEAF_PER_KEY DATA_PER_KEY---------- ---------- ---------- ---------- ---------- ---------- ------------ ------------T_PK                1          2       1000       1000        978            1            1T_VAL1_I            1          2        445        509        500            1            1T_VAL2_I            1          3          6       1000        176            1           29

             这里的几列具体含义是：

①blevel：也就是B-Tree level，比如从根到支再到叶，blevel为2，但索引的高度是blevel+1也就是3。

②leaf_block：索引中的叶子块数。

③distinct_keys：索引中的唯一键值总数。

④num_rows：索引中的键值数。

⑤clustering_factor：聚簇因子，它用来表征索引和数据之间的排序程度。这个因子的最小值是表里非空数据块的个数，最大值是索引的键数。下面研究如何计算聚簇因子。

⑥avg_leaf_blocks_per_key：这个参数表示存放一个键值的平均叶子块数。计算公式为：avg_leaf_blocks_per_key≈leaf_blocks/distinct_keys。

⑦avg_data_blocks_per_key：它表示表中单个键引用的平均数据块。计算公式为：avg_data_blocks_per_key≈clustering_factor/distinct_keys。

二、如何计算聚簇因子

             下面是计算聚簇因子的脚本，

CREATE OR REPLACE FUNCTION clustering_factor (  p_owner IN VARCHAR2,   p_table_name IN VARCHAR2,  p_column_name IN VARCHAR2) RETURN NUMBER IS  l_cursor             SYS_REFCURSOR;  l_clustering_factor  BINARY_INTEGER := 0;  l_block_nr           BINARY_INTEGER := 0;  l_previous_block_nr  BINARY_INTEGER := 0;  l_file_nr            BINARY_INTEGER := 0;  l_previous_file_nr   BINARY_INTEGER := 0;  BEGIN  OPEN l_cursor FOR     'SELECT dbms_rowid.rowid_block_number(rowid) block_nr, '||    '       dbms_rowid.rowid_to_absolute_fno(rowid, '''||                                             p_owner||''','''||                                             p_table_name||''') file_nr '||    'FROM '||p_owner||'.'||p_table_name||' '||    'WHERE '||p_column_name||' IS NOT NULL '||    'ORDER BY ' || p_column_name;  LOOP    FETCH l_cursor INTO l_block_nr, l_file_nr;    EXIT WHEN l_cursor%NOTFOUND;    IF (l_previous_block_nr <> l_block_nr OR l_previous_file_nr <> l_file_nr)    THEN      l_clustering_factor := l_clustering_factor + 1;    END IF;    l_previous_block_nr := l_block_nr;    l_previous_file_nr := l_file_nr;  END LOOP;  CLOSE l_cursor;  RETURN l_clustering_factor;END;/

             这个函数表示的一些含义说明一下，首先定义了一个函数，包含三个参数：所属、表名、列名，还定义了若干个返回值变量。接着定义了一个游标，该游标是根据所传入的参数，返回每条记录所在的块号、文件号。接着遍历游标，提取每一个记录的数据块号与文件号，若数据块号不与前一个数据块号相同，或者文件号不与前一个文件号相同，则聚簇因子加一。

            下面验证这个算法的正确性，

SELECT I.INDEX_NAME,       I.CLUSTERING_FACTOR,       CLUSTERING_FACTOR(USER, I.TABLE_NAME, IC.COLUMN_NAME) AS MY_CLSTF  FROM USER_INDEXES I, USER_IND_COLUMNS IC WHERE I.TABLE_NAME = 'T'   AND I.INDEX_NAME = IC.INDEX_NAME;INDEX_NAME                     CLUSTERING_FACTOR   MY_CLSTF------------------------------ ----------------- ----------T_PK                                         972        972T_VAL1_I                                     506        506T_VAL2_I                                     178        178

             可以看出，结果中用脚本中的函数算出的聚簇因子与oracle自带的clustering_factor列结果完全一致。