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
/
索引分析脚本
最新推荐文章于 2022-10-20 14:52:55 发布