查看数据库中有无多余的索引即一个索引的字段为另一个索引的前几个字段如index的定义为test(filedfiled)index的定义为test(filedfiledfiled)则认为index是多余的
column redundant_index format a
column sufficient_index format a
select /*+ ordered */
oname||||nname redundant_index
oname||||nname sufficient_index
from
(
select
obj#
bo#
count(*) cols
max(decode(pos# intcol#)) leadcol#
from
sysicol$
group by
obj#
bo#
) ic
sysicol$ ic
sysind$ i
sysobj$ n
sysobj$ n
sysuser$ o
sysuser$ o
where
icobj# != icobj# and
icbo# = icbo# and
icpos# = and
icintcol# = icleadcol# and
iobj# = icobj# and
bitand(iproperty ) = and
ls * (ls + ) / =
( select
sum(xcpos#)
from
sysicol$ xc
sysicol$ xc
where
xcobj# = icobj# and
xcobj# = icobj# and
xcpos# = xcpos# and
xcintcol# = xcintcol#
) and
nobj# = icobj# and
nobj# = icobj# and
ouser# = nowner# and
ouser# = nowner#
