Index rebuild – Clustering factor to Block ratio

column table_name format a20 heading ‘Table|Name’
column index_name format a20 heading ‘Index|Name’
column dirty_blocks heading ‘Dirty|Blocks’
column clustering_factor heading ‘Clustering|Factor’
column cfb_ratio heading ‘Clustering Factor|To Blocks Ratio’ format 99,999.99
column owner format a15 heading ‘Owner’
ttitle ‘Clustering Factor to Block Ratio Report’
set lines 132 verify off pages 55 feedback off
break on owner on table_name
select t.owner,t.table_name,i.index_name, t.num_rows, t.blocks dirty_blocks,i.clustering_factor,
i.clustering_factor/decode(t.blocks,0,decode(i.clustering_factor,0,1,i.clustering_factor),t.blocks) cfb_ratio, i.blevel
from dba_tables t, dba_indexes i
where t.owner=i.table_owner and t.table_name=i.table_name
and i.clustering_factor/decode(t.blocks,0,decode(i.clustering_factor,0,1,i.clustering_factor),t.blocks)>10
order by t.owner,i.clustering_factor/decode(t.blocks,0,decode(i.clustering_factor,0,1,i.clustering_factor),t.blocks) desc,t.table_name,i.index_name
set lines 80 pages 22 feedback on verify on
clear columns
ttitle off

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: