-
Notifications
You must be signed in to change notification settings - Fork 65
/
index_drops.sql
184 lines (172 loc) · 6.46 KB
/
index_drops.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
/* index_drops.sql
Copyright (C) 2013 Sean Scott
This program is free software; you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation; either version 2 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License along
with this program; if not, write to the Free Software Foundation, Inc.,
51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA.
These scripts provide a variety of different methods for looking at indexes that
are candidates to be dropped. */
/* Indexes that don't have statistics */
select index_name
from dba_indexes
where index_name not in (
select index_name
from v$segment_statistics);
/* Indexes that are candidates for being dropped (not part of a constraint, not in
an execution plan). */
select owner
, index_name
from dba_indexes
where (owner, index_name) not in (
select owner, object_name
from dba_hist_sql_plan
where object_type = 'INDEX') --Exclude indexes that have been used in an execution plan.
and (owner, index_name) not in (
select owner, index_name
from dba_constraints
where constraint_type in ('P','R')) --Exclude indexes that are part of a referential integrity constraint.
order by 1;
column cardinality format 999,999,999.90
column sample_pct format 999,999,999.90
/* Indexes that may be empty/droppable because they have 0 distinct keys, 0 rows,
and aren't part of a PK/FK constraint. */
select owner
, index_name
, uniqueness
, index_type
, num_rows
, decode(distinct_keys, NULL, 0, 0, 0, distinct_keys/num_rows) as cardinality
, status
, last_analyzed
from dba_indexes
where distinct_keys = 0
and num_rows = 0
and (owner, index_name) not in (
select owner, index_name
from dba_constraints
where constraint_type in ('P','R'));
/* Small indexes that may be droppable because they're small and not used in a
FK/PK constraint. Sometimes, a FTS may be as good or better than doing an index
lookup against a tiny index/table. */
select owner
, index_name
, pct_free
, logging
, index_type
, distinct_keys
, num_rows
, decode(distinct_keys, NULL, 0, 0, 0, distinct_keys/num_rows) as cardinality
, status
, last_analyzed
, decode(distinct_keys, NULL, 0, 0, 0, distinct_keys/sample_size) * 100 as sample_pct
from dba_indexes
where (distinct_keys < 20
or num_rows < 20)
and distinct_keys != 0
and num_rows != 0
and (owner, index_name) not in (
select owner, index_name
from dba_constraints
where constraint_type in ('P','R'))
order by 8 desc, 1, 2;
select owner
, index_name
, pct_free
, logging
, index_type
, distinct_keys
, num_rows
, decode(distinct_keys, NULL, 0, 0, 0, distinct_keys/num_rows) as cardinality
, status
, last_analyzed
, decode(distinct_keys, NULL, 0, 0, 0, distinct_keys/sample_size) * 100 as sample_pct
from dba_indexes
where uniqueness != 'UNIQUE'
and distinct_keys != 0
and num_rows != 0
order by 8 desc, 1, 2;
/* Identify potentially redundant indexes based on predicates */
column column_name_list format a50
column column_name_list_dup format a50
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 dba_ind_columns
where index_owner in ('&OWNER'))
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 dba_ind_columns
where index_owner in ('&OWNER'))
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;