查出索引的聚集因子

其实查询索引的聚集因子，如果是组合索引，一个sql应该搞不定的，如果是单字段索引，一个sql还是可以搞定的。我做了一个存储过程，用来查询组合索引的聚集因子。

1.首先创建一个表：

create table sql_test(
table_name varchar2(2000),
index_name varchar2(2000),
sql_text clob,
row_cnt number(10),
block_cnt number(10),
clu_fac_num number(10),
fac_to_block_per number(10,2)
);

2.创建存储过程：

CREATE OR REPLACE PROCEDURE get_factor_and_block(v_username VARCHAR2) AS

  v_clu_fac_num      NUMBER(10);
  v_row_cnt          NUMBER(10);
  v_block_cnt        NUMBER(10);
  v_sql              CLOB;
  v_fac_to_block_per NUMBER(10, 2);
BEGIN
  DELETE FROM sql_test;
  FOR i IN (WITH u AS
               (SELECT v_username owner FROM dual)
              SELECT t.table_name,
                     t.index_name,
                     listagg('"' || t.column_name || '"', ',') within GROUP(ORDER BY column_position) column_name
              FROM   dba_ind_columns t, u
              WHERE  t.index_owner = u.owner
              GROUP  BY t.table_name, t.index_name)
  LOOP
    v_sql := 'SELECT SUM(CASE WHEN lead_block# != block# THEN 1 ELSE 0 END) + 1,count(distinct block#),decode(count(distinct block#),0,0,null,0,round((SUM(CASE WHEN lead_block# != block# THEN 1 ELSE 0 END) + 1)/count(distinct block#),4) * 100),count(1) FROM   ( SELECT ' ||
             i.column_name ||
             ', dbms_rowid.rowid_relative_fno(ROWID) file#, lag(dbms_rowid.rowid_block_number(ROWID)) over(ORDER BY ' ||
             i.column_name ||
             ') lead_block#, dbms_rowid.rowid_block_number(ROWID) block#, dbms_rowid.rowid_row_number(ROWID) row# FROM ' ||
             i.table_name || ' ORDER  BY ' || i.column_name || ') t';
    EXECUTE IMMEDIATE v_sql
      INTO v_clu_fac_num, v_block_cnt, v_fac_to_block_per, v_row_cnt;
    INSERT INTO sql_test
    VALUES
      (i.table_name,
       i.index_name,
       v_sql,
       v_row_cnt,
       v_block_cnt,
       v_clu_fac_num,
       v_fac_to_block_per);
  END LOOP;
  COMMIT;
END;

3.最后查询建立的表信息：

完成。