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;
浙公网安备 33010602011771号