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;