인덱스 구성 컬럼 추출 쿼리

SELECT  TABLE_OWNER,

        TABLE_NAME,

        INDEX_NAME,

        UNIQUE_RULE,

        LTRIM(SYS_CONNECT_BY_PATH(COLUMN_NAME,', '),', ')  INDEX_COLUMN

FROM   (

       

SELECT  A.TABLE_OWNER,

                A.TABLE_NAME,

                A.INDEX_NAME,

                B.UNIQUENESS UNIQUE_RULE,

                A.COLUMN_POSITION POSISION,

                A.COLUMN_NAME,

                ROW_NUMBER() OVER(PARTITION BY A.TABLE_OWNER, A.TABLE_NAME, A.INDEX_NAME ORDER BY A.TABLE_OWNER, A.TABLE_NAME, A.INDEX_NAME, A.COLUMN_POSITION ) RNUM,

                COUNT(*) OVER(PARTITION BY A.TABLE_OWNER, A.TABLE_NAME, A.INDEX_NAME) CNT

        FROM    DBA_IND_COLUMNS  A,

                DBA_INDEXES      B

        WHERE   A.TABLE_OWNER = B.TABLE_OWNER

        AND     A.TABLE_NAME = B.TABLE_NAME

        AND     A.INDEX_NAME = B.INDEX_NAME

        AND     B.TABLE_OWNER IN ('SEMIHOW')

        ORDER BY A.TABLE_OWNER, A.TABLE_NAME, A.INDEX_NAME, A.COLUMN_POSITION

       )

WHERE   LEVEL = CNT

CONNECT BY RNUM = PRIOR RNUM + 1

    AND INDEX_NAME = PRIOR INDEX_NAME

    AND TABLE_OWNER = PRIOR TABLE_OWNER

    AND TABLE_NAME = PRIOR TABLE_NAME

ORDER BY TABLE_OWNER, TABLE_NAME, INDEX_NAME

 

출처 : http://www.dator.co.kr/data24hh/textyle/43735