查看數據庫中有無多余的索引SQL

　　查看數據庫中有無多余的索引即一個索引的字段為另一個索引的前幾個字段如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#

From:http://tw.wingwit.com/Article/program/Oracle/201311/17500.html