duplicate index

WITH    WITH_IND_COLUMNS
AS     (
        SELECT  TABLE_OWNER, TABLE_NAME, INDEX_OWNER, INDEX_NAME
              , MIN(CASE WHEN COLUMN_POSITION =  1 THEN          COLUMN_NAME  || ' ' || DESCEND END)
             || MIN(CASE WHEN COLUMN_POSITION =  2 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)
             || MIN(CASE WHEN COLUMN_POSITION =  3 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)
             || MIN(CASE WHEN COLUMN_POSITION =  4 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)
             || MIN(CASE WHEN COLUMN_POSITION =  5 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)
             || MIN(CASE WHEN COLUMN_POSITION =  6 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)
             || MIN(CASE WHEN COLUMN_POSITION =  7 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)
             || MIN(CASE WHEN COLUMN_POSITION =  8 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)
             || MIN(CASE WHEN COLUMN_POSITION =  9 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)
             || MIN(CASE WHEN COLUMN_POSITION = 10 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)
             || MIN(CASE WHEN COLUMN_POSITION = 11 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)
             || MIN(CASE WHEN COLUMN_POSITION = 12 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)
             || MIN(CASE WHEN COLUMN_POSITION = 13 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)
             || MIN(CASE WHEN COLUMN_POSITION = 14 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)
             || MIN(CASE WHEN COLUMN_POSITION = 15 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)
             || MIN(CASE WHEN COLUMN_POSITION = 16 THEN ' + ' || COLUMN_NAME  || ' ' || DESCEND END)
                AS IND_COLUMNS
        FROM    DBA_IND_COLUMNS A
        WHERE   1 = 1
        AND     A.TABLE_OWNER NOT IN
                (
                'ANONYMOUS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','WMSYS','XDB','XS$NULL'
                )
        AND     A.TABLE_NAME NOT LIKE 'BIN$%'
        --AND     A.TABLE_OWNER LIKE 'OXY%'    /* 분석대상 OWNER 이름을 입력할 것 */
        --AND     A.TABLE_NAME LIKE 'TB%'
        GROUP BY
                TABLE_OWNER, TABLE_NAME, INDEX_OWNER, INDEX_NAME
       )
      , WITH_CONSTRAINTS
AS     (
        SELECT  A.OWNER AS TABLE_OWNER, A.TABLE_NAME, A.INDEX_OWNER, A.INDEX_NAME
              , MIN(CASE WHEN A.CONSTRAINT_TYPE = 'P' THEN 'Y' END) AS IS_PRIMARY_KEY
              , MIN(CASE WHEN A.CONSTRAINT_TYPE = 'U' THEN 'Y' END) AS IS_UNIQUE_CONSTRAINT
        FROM    DBA_CONSTRAINTS A
        WHERE   1 = 1
        AND     A.OWNER NOT IN
                (
                'ANONYMOUS','APEX_030200','APEX_PUBLIC_USER','APPQOSSYS','CTXSYS','DBSNMP','DIP','EXFSYS','FLOWS_FILES','LBACSYS','MDDATA','MDSYS','MGMT_VIEW','OLAPSYS','ORACLE_OCM','ORDDATA','ORDPLUGINS','ORDSYS','OUTLN','OWBSYS','OWBSYS_AUDIT','SCOTT','SI_INFORMTN_SCHEMA','SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','SYS','SYSMAN','SYSTEM','WMSYS','XDB','XS$NULL'
                )
        AND     A.TABLE_NAME NOT LIKE 'BIN$%'
        AND     A.CONSTRAINT_TYPE IN ('P' , 'U')
        GROUP BY
                A.OWNER, A.TABLE_NAME, A.INDEX_OWNER, A.INDEX_NAME
       )
SELECT  ROW_NUMBER() OVER (ORDER BY A.TABLE_OWNER, A.TABLE_NAME, A.INDEX_NAME, B.INDEX_NAME) AS "번호"
      , A.TABLE_OWNER, A.TABLE_NAME
      ,(
        SELECT  X.COMMENTS
        FROM    DBA_TAB_COMMENTS X
        WHERE   X.OWNER = A.TABLE_OWNER
        AND     X.TABLE_NAME = A.TABLE_NAME
       ) AS "엔티티명"

      , A.INDEX_OWNER, A.INDEX_NAME, A.IND_COLUMNS
      , E.IS_PRIMARY_KEY, E.IS_UNIQUE_CONSTRAINT, C.UNIQUENESS

      , B.INDEX_NAME AS "포함하는 인덱스", B.IND_COLUMNS "포함하는 인덱스 칼럼"
      , F.IS_PRIMARY_KEY, F.IS_UNIQUE_CONSTRAINT, D.UNIQUENESS

FROM    WITH_IND_COLUMNS A
      , WITH_IND_COLUMNS B
      , DBA_INDEXES      C
      , DBA_INDEXES      D
      , WITH_CONSTRAINTS E
      , WITH_CONSTRAINTS F
WHERE   B.TABLE_OWNER   = A.TABLE_OWNER
AND     B.TABLE_NAME    = A.TABLE_NAME
AND     B.INDEX_NAME   <> A.INDEX_NAME
AND    (
        B.IND_COLUMNS LIKE A.IND_COLUMNS || ' + ' || '%'
OR      B.IND_COLUMNS = A.IND_COLUMNS
       )
--AND     NOT EXISTS
--       (
--        /* PK 또는 Unique 제약을 가진 인덱스는 제외 */
--        SELECT  1
--        FROM    DBA_CONSTRAINTS X
--        WHERE   X.CONSTRAINT_TYPE IN ('P' , 'U')
--        AND     X.OWNER = A.TABLE_OWNER
--        AND     X.TABLE_NAME = A.TABLE_NAME
--        AND     X.INDEX_OWNER = A.INDEX_OWNER
--        AND     X.INDEX_NAME = A.INDEX_NAME
--       )
--        /* Unique 인덱스는 제외 */

--AND     C.UNIQUENESS <> 'UNIQUE'
AND     A.INDEX_OWNER   = C.OWNER
AND     A.INDEX_NAME    = C.INDEX_NAME

AND     B.INDEX_OWNER   = D.OWNER
AND     B.INDEX_NAME    = D.INDEX_NAME

AND     A.TABLE_OWNER   = E.TABLE_OWNER(+)
AND     A.TABLE_NAME    = E.TABLE_NAME(+)
AND     A.INDEX_OWNER   = E.INDEX_OWNER(+)
AND     A.INDEX_NAME    = E.INDEX_NAME(+)

AND     B.TABLE_OWNER   = F.TABLE_OWNER(+)
AND     B.TABLE_NAME    = F.TABLE_NAME(+)
AND     B.INDEX_OWNER   = F.INDEX_OWNER(+)
AND     B.INDEX_NAME    = F.INDEX_NAME(+)

ORDER BY
        A.TABLE_OWNER, A.TABLE_NAME, A.INDEX_NAME, B.INDEX_NAME
;