create or replace function clustering_factor(p_owner in varchar2,p_table_name in varchar2,p_column_name in varchar2)return number isl_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;1636select i.index_name,i.clustering_factor,clustering_factor(user,i.table_name,ic.COLUMN_NAME) as my_clstffrom 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 508 508T_VAL2_I 174 174
转载于:https://www.cnblogs.com/iyoume2008/p/6930995.html
相关资源：JAVA上百实例源码以及开源项目