1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
| set linesize 300
spool index_info.txt
SELECT i.table_name, i.index_name, t.num_rows, t.blocks, i.clustering_factor,
case when nvl(i.clustering_factor,0) = 0 then 'No Stats'
when nvl(t.num_rows,0) = 0 then 'No Stats'
when (round(i.clustering_factor / t.num_rows) * 100) < 6 then 'Excellent '
when (round(i.clustering_factor / t.num_rows) * 100) between 7 and 11 then 'Good'
when (round(i.clustering_factor / t.num_rows) * 100) between 12 and 21 then 'Fair'
else 'Poor'
end Index_Quality,
i.avg_data_blocks_per_key, i.avg_leaf_blocks_per_key,
to_char(o.created,'MM/DD/YYYY HH24:MI:SSSSS') Created
from user_indexes i, user_objects o, user_tables t
where i.index_name = o.object_name
and i.table_name = t.table_name
order by 1;
spool off |
Partager