查看有哪些索引含有重复的字段, 从而让索引更加合理化!
SQL> SELECT /*+ rule */ a .table_owner,
           a.table_name,
           a.index_owner,
           a.index_name,
           column_name_list,
           column_name_list_dup,
           dup duplicate_indexes,
           i.uniqueness,
           i.partitioned,
           i.leaf_blocks,
           i.distinct_keys,
           i.num_rows,
           i.clustering_factor
    FROM   (SELECT   table_owner,
                     table_name,
                     index_owner,
                     index_name,
                     column_name_list_dup,
                     dup,
                     MAX (dup)
                        OVER (PARTITION BY table_owner, table_name, index_name)
                        dup_mx
              FROM   (    SELECT   table_owner,
                                   table_name,
                                   index_owner,
                                   index_name,
                                   SUBSTR (SYS_CONNECT_BY_PATH (column_name, ','),
                                           2)
                                      column_name_list_dup,
                                   dup
                            FROM   (SELECT   index_owner,
                                             index_name,
                                             table_owner,
                                             table_name,
                                             column_name,
                                             COUNT(1)
                                                OVER (
                                                   PARTITION BY index_owner,
                                                                index_name
                                                )
                                                cnt,
                                             ROW_NUMBER ()
                                                OVER (
                                                   PARTITION BY index_owner,
                                                                index_name
                                                   ORDER BY column_position
                                                )
                                                AS seq,
                                             COUNT(1)
                                                OVER (
                                                   PARTITION BY table_owner,
                                                                table_name,
                                                                column_name,
                                                                column_position
                                                )
                                                AS dup
                                      FROM   sys.dba_ind_columns
                                     WHERE   (   index_owner LIKE 'E%'
                                              OR index_owner LIKE 'TRIAL%'
                                              OR index_owner = 'SCOTT')
                                             AND index_owner NOT IN ('EXFSYS'))
                           WHERE   dup != 1
                      START WITH   seq = 1
                      CONNECT BY       PRIOR seq + 1 = seq
                                   AND PRIOR index_owner = index_owner
                                   AND PRIOR index_name = index_name)) a,
           (    SELECT   table_owner,
                         table_name,
                         index_owner,
                         index_name,
                         SUBSTR (SYS_CONNECT_BY_PATH (column_name, ','), 2)
                            column_name_list
                  FROM   (SELECT   index_owner,
                                   index_name,
                                   table_owner,
                                   table_name,
                                   column_name,
                                   COUNT (1)
                                      OVER (PARTITION BY index_owner, index_name)
                                      cnt,
                                   ROW_NUMBER ()
                                      OVER (PARTITION BY index_owner, index_name
                                            ORDER BY column_position)
                                      AS seq
                            FROM   sys.dba_ind_columns
                           WHERE   (   index_owner LIKE 'E%'
                                    OR index_owner LIKE 'TRIAL%'
                                    OR index_owner = 'SCOTT')
                                   AND index_owner NOT IN ('EXFSYS'))
                 WHERE   seq = cnt
            START WITH   seq = 1
            CONNECT BY       PRIOR seq + 1 = seq
                         AND PRIOR index_owner = index_owner
                         AND PRIOR index_name = index_name) b,
           dba_indexes i
   WHERE       a.dup = a.dup_mx
           AND a.index_owner = b.index_owner
           AND a.index_name = b.index_name
           AND a.index_owner = i.owner
           AND a.index_name = i.index_name
ORDER BY   a.table_owner, a.table_name, column_name_list_dup;

例如，在scott用户下面的emp表的empno上创建一个索引，然后再empno,deptno2列上创建复合索引，使用以上语句，查询的结果如下所示

SCOTT EMP01 SCOTT I_EMPNO_EMP01 EMPNO EMPNO

SCOTT EMP01 SCOTT I_EMPNO_DEPTNO EMPNO,DEPTNO EMPNO

ITPub博客

oracle数据库查询重复的索引列

最新文章