索引分析

索引分析
 select
      idx.owner owner,
      idx.table_name tablename,
      idx.index_name index_name,
      idx.blocks idx_blocks,
      tbl.blocks tbl_blocks,
      trunc(idx.blocks/tbl.blocks*100)/100 pct
from
  (select i.owner owner ,i.index_name index_name,
      SUM(S1.blocks) blocks,i.table_owner table_owner,
    i.table_name table_name
    from dba_segments s1,dba_indexes i
    where
     s1.owner=i.owner and s1.segment_name=i.index_name and
i.owner not in('SYS','SYSTEM')
    GROUP BY i.owner ,i.index_name ,i.table_owner , i.table_name  ) idx,
(select t.owner owner ,t.table_name table_name,SUM(s2.blocks) blocks from dba_segments s2,dba_tables t where
     s2.owner=t.owner and s2.segment_name=t.table_name and
     t.owner not in ('SYS','SYSTEM')
     GROUP BY  T.OWNER,T.TABLE_NAME
     ) tbl
where
   idx.table_owner=tbl.owner and
   idx.table_name=tbl.table_name and
   (idx.blocks/tbl.blocks)>0.5 and
   idx.blocks>200
   order by 4
/