Oracle删除重复索引

  1 查看有哪些索引含有重复的字段, 从而让索引更加合理化!
  2 SQL> SELECT /*+ rule */ a .table_owner,
  3            a.table_name,
  4            a.index_owner,
  5            a.index_name,
  6            column_name_list,
  7            column_name_list_dup,
  8            dup duplicate_indexes,
  9            i.uniqueness,
 10            i.partitioned,
 11            i.leaf_blocks,
 12            i.distinct_keys,
 13            i.num_rows,
 14            i.clustering_factor
 15     FROM   (SELECT   table_owner,
 16                      table_name,
 17                      index_owner,
 18                      index_name,
 19                      column_name_list_dup,
 20                      dup,
 21                      MAX (dup)
 22                         OVER (PARTITION BY table_owner, table_name, index_name)
 23                         dup_mx
 24               FROM   (    SELECT   table_owner,
 25                                    table_name,
 26                                    index_owner,
 27                                    index_name,
 28                                    SUBSTR (SYS_CONNECT_BY_PATH (column_name, ','),
 29                                            2)
 30                                       column_name_list_dup,
 31                                    dup
 32                             FROM   (SELECT   index_owner,
 33                                              index_name,
 34                                              table_owner,
 35                                              table_name,
 36                                              column_name,
 37                                              COUNT(1)
 38                                                 OVER (
 39                                                    PARTITION BY index_owner,
 40                                                                 index_name
 41                                                 )
 42                                                 cnt,
 43                                              ROW_NUMBER ()
 44                                                 OVER (
 45                                                    PARTITION BY index_owner,
 46                                                                 index_name
 47                                                    ORDER BY column_position
 48                                                 )
 49                                                 AS seq,
 50                                              COUNT(1)
 51                                                 OVER (
 52                                                    PARTITION BY table_owner,
 53                                                                 table_name,
 54                                                                 column_name,
 55                                                                 column_position
 56                                                 )
 57                                                 AS dup
 58                                       FROM   sys.dba_ind_columns
 59                                      WHERE   (   index_owner LIKE 'E%'
 60                                               OR index_owner LIKE 'TRIAL%'
 61                                               OR index_owner = 'SCOTT')
 62                                              AND index_owner NOT IN ('EXFSYS'))
 63                            WHERE   dup != 1
 64                       START WITH   seq = 1
 65                       CONNECT BY       PRIOR seq + 1 = seq
 66                                    AND PRIOR index_owner = index_owner
 67                                    AND PRIOR index_name = index_name)) a,
 68            (    SELECT   table_owner,
 69                          table_name,
 70                          index_owner,
 71                          index_name,
 72                          SUBSTR (SYS_CONNECT_BY_PATH (column_name, ','), 2)
 73                             column_name_list
 74                   FROM   (SELECT   index_owner,
 75                                    index_name,
 76                                    table_owner,
 77                                    table_name,
 78                                    column_name,
 79                                    COUNT (1)
 80                                       OVER (PARTITION BY index_owner, index_name)
 81                                       cnt,
 82                                    ROW_NUMBER ()
 83                                       OVER (PARTITION BY index_owner, index_name
 84                                             ORDER BY column_position)
 85                                       AS seq
 86                             FROM   sys.dba_ind_columns
 87                            WHERE   (   index_owner LIKE 'E%'
 88                                     OR index_owner LIKE 'TRIAL%'
 89                                     OR index_owner = 'SCOTT')
 90                                    AND index_owner NOT IN ('EXFSYS'))
 91                  WHERE   seq = cnt
 92             START WITH   seq = 1
 93             CONNECT BY       PRIOR seq + 1 = seq
 94                          AND PRIOR index_owner = index_owner
 95                          AND PRIOR index_name = index_name) b,
 96            dba_indexes i
 97    WHERE       a.dup = a.dup_mx
 98            AND a.index_owner = b.index_owner
 99            AND a.index_name = b.index_name
100            AND a.index_owner = i.owner
101            AND a.index_name = i.index_name
102 ORDER BY   a.table_owner, a.table_name, column_name_list_dup;