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 clustering_factor;


1636
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        972
T_VAL1_I                                     508        508
T_VAL2_I                                     174        174

索引聚簇因子相关