查看数据库中有无多余的索引SQL

        column redundant_index format a39
        column sufficient_index format a39
        select /*+ ordered */
        o1.name||'.'||n1.name redundant_index,
        o2.name||'.'||n2.name sufficient_index
        from
        (
        select
        obj#,
        bo#,
        count(*) cols,
        max(decode(pos#, 1, intcol#)) leadcol#
        from
        sys.icol$
        group by
        obj#,
        bo#
        ) ic1,
        sys.icol$ ic2,
        sys.ind$ i1,
        sys.obj$ n1,
        sys.obj$ n2,
        sys.user$ o1,
        sys.user$ o2
        where
        ic2.obj# != ic1.obj# and
        ic2.bo# = ic1.bo# and
        ic2.pos# = 1 and
        ic2.intcol# = ic1.leadcol# and
        i1.obj# = ic1.obj# and
        bitand(i1.property, 1) = 0 and
        ic1.cols * (ic1.cols + 1) / 2 =
        ( select
        sum(xc1.pos#)
        from
        sys.icol$ xc1,
        sys.icol$ xc2
        where
        xc1.obj# = ic1.obj# and
        xc2.obj# = ic2.obj# and
        xc1.pos# = xc2.pos# and
        xc1.intcol# = xc2.intcol#
        ) and
        n1.obj# = ic1.obj# and
        n2.obj# = ic2.obj# and
        o1.user# = n1.owner# and
        o2.user# = n2.owner#

--------------------next---------------------