Module: IndexShotgun::Analyzer

Defined in:
lib/index_shotgun/analyzer.rb

Defined Under Namespace

Classes: Response

Class Method Summary collapse

Class Method Details

.check_indexes(table) ⇒ Array<Hash>

check duplicate indexes of table

Parameters:

  • table (String) —

    table name

Returns:

  • (Array<Hash>) —

    array of index info index: index info ActiveRecord::ConnectionAdapters::IndexDefinition result: search result message



75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
 
# File 'lib/index_shotgun/analyzer.rb', line 75

def check_indexes(table)
  indexes = table_indexes(table)

  indexes.permutation(2).each_with_object([]) do |(source_index, target_index), response|
    next unless source_index.columns.start_with?(target_index.columns)

    if target_index.unique
      response << {
        index:  source_index,
        result: "#{source_index.name} has column(s) on the right side of unique index (#{target_index.name}). You can drop if low cardinality",
      }
    else
      response << {
        index:  target_index,
        result: "#{target_index.name} is a left-prefix of #{source_index.name}",
      }
    end
  end
end

.exclude_tables ⇒ Object 



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
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
 
# File 'lib/index_shotgun/analyzer.rb', line 102

def exclude_tables
  return @exclude_tables if @exclude_tables

  # Rails default tables
  tables = %w(ar_internal_metadata schema_migrations)

  # Oracle system tables
  tables += %w(
    AQ$DEF$_AQCALL
    AQ$DEF$_AQERROR
    AQ$_DEF$_AQCALL_F
    AQ$_DEF$_AQERROR_F
    AQ$_INTERNET_AGENTS
    AQ$_INTERNET_AGENT_PRIVS
    AQ$_QUEUES
    AQ$_QUEUE_TABLES
    AQ$_SCHEDULES
    CATALOG
    COL
    DEF$_AQCALL
    DEF$_AQERROR
    DEF$_CALLDEST
    DEF$_DEFAULTDEST
    DEF$_DESTINATION
    DEF$_ERROR
    DEF$_LOB
    DEF$_ORIGIN
    DEF$_PROPAGATOR
    DEF$_PUSHED_TRANSACTIONS
    HELP
    LOGMNRC_DBNAME_UID_MAP
    LOGMNRC_GSBA
    LOGMNRC_GSII
    LOGMNRC_GTCS
    LOGMNRC_GTLO
    LOGMNRP_CTAS_PART_MAP
    LOGMNRT_MDDL$
    LOGMNR_AGE_SPILL$
    LOGMNR_ATTRCOL$
    LOGMNR_ATTRIBUTE$
    LOGMNR_CCOL$
    LOGMNR_CDEF$
    LOGMNR_COL$
    LOGMNR_COLTYPE$
    LOGMNR_DICTIONARY$
    LOGMNR_DICTSTATE$
    LOGMNR_ENC$
    LOGMNR_ERROR$
    LOGMNR_FILTER$
    LOGMNR_GLOBAL$
    LOGMNR_GT_TAB_INCLUDE$
    LOGMNR_GT_USER_INCLUDE$
    LOGMNR_GT_XID_INCLUDE$
    LOGMNR_ICOL$
    LOGMNR_IND$
    LOGMNR_INDCOMPART$
    LOGMNR_INDPART$
    LOGMNR_INDSUBPART$
    LOGMNR_INTEGRATED_SPILL$
    LOGMNR_KOPM$
    LOGMNR_LOB$
    LOGMNR_LOBFRAG$
    LOGMNR_LOG$
    LOGMNR_LOGMNR_BUILDLOG
    LOGMNR_NTAB$
    LOGMNR_OBJ$
    LOGMNR_OPQTYPE$
    LOGMNR_PARAMETER$
    LOGMNR_PARTOBJ$
    LOGMNR_PROCESSED_LOG$
    LOGMNR_PROPS$
    LOGMNR_REFCON$
    LOGMNR_RESTART_CKPT$
    LOGMNR_RESTART_CKPT_TXINFO$
    LOGMNR_SEED$
    LOGMNR_SESSION$
    LOGMNR_SESSION_ACTIONS$
    LOGMNR_SESSION_EVOLVE$
    LOGMNR_SPILL$
    LOGMNR_SUBCOLTYPE$
    LOGMNR_TAB$
    LOGMNR_TABCOMPART$
    LOGMNR_TABPART$
    LOGMNR_TABSUBPART$
    LOGMNR_TS$
    LOGMNR_TYPE$
    LOGMNR_UID$
    LOGMNR_USER$
    LOGSTDBY$APPLY_MILESTONE
    LOGSTDBY$APPLY_PROGRESS
    LOGSTDBY$EDS_TABLES
    LOGSTDBY$EVENTS
    LOGSTDBY$FLASHBACK_SCN
    LOGSTDBY$HISTORY
    LOGSTDBY$PARAMETERS
    LOGSTDBY$PLSQL
    LOGSTDBY$SCN
    LOGSTDBY$SKIP
    LOGSTDBY$SKIP_SUPPORT
    LOGSTDBY$SKIP_TRANSACTION
    MVIEW$_ADV_AJG
    MVIEW$_ADV_BASETABLE
    MVIEW$_ADV_CLIQUE
    MVIEW$_ADV_ELIGIBLE
    MVIEW$_ADV_EXCEPTIONS
    MVIEW$_ADV_FILTER
    MVIEW$_ADV_FILTERINSTANCE
    MVIEW$_ADV_FJG
    MVIEW$_ADV_GC
    MVIEW$_ADV_INFO
    MVIEW$_ADV_JOURNAL
    MVIEW$_ADV_LEVEL
    MVIEW$_ADV_LOG
    MVIEW$_ADV_OUTPUT
    MVIEW$_ADV_PARAMETERS
    MVIEW$_ADV_PLAN
    MVIEW$_ADV_PRETTY
    MVIEW$_ADV_ROLLUP
    MVIEW$_ADV_SQLDEPEND
    MVIEW$_ADV_TEMP
    MVIEW$_ADV_WORKLOAD
    MVIEW_EVALUATIONS
    MVIEW_EXCEPTIONS
    MVIEW_FILTER
    MVIEW_FILTERINSTANCE
    MVIEW_LOG
    MVIEW_RECOMMENDATIONS
    MVIEW_WORKLOAD
    OL$
    OL$HINTS
    OL$NODES
    PRODUCT_PRIVS
    PRODUCT_USER_PROFILE
    PUBLICSYN
    REPCAT$_AUDIT_ATTRIBUTE
    REPCAT$_AUDIT_COLUMN
    REPCAT$_COLUMN_GROUP
    REPCAT$_CONFLICT
    REPCAT$_DDL
    REPCAT$_EXCEPTIONS
    REPCAT$_EXTENSION
    REPCAT$_FLAVORS
    REPCAT$_FLAVOR_OBJECTS
    REPCAT$_GENERATED
    REPCAT$_GROUPED_COLUMN
    REPCAT$_INSTANTIATION_DDL
    REPCAT$_KEY_COLUMNS
    REPCAT$_OBJECT_PARMS
    REPCAT$_OBJECT_TYPES
    REPCAT$_PARAMETER_COLUMN
    REPCAT$_PRIORITY
    REPCAT$_PRIORITY_GROUP
    REPCAT$_REFRESH_TEMPLATES
    REPCAT$_REPCAT
    REPCAT$_REPCATLOG
    REPCAT$_REPCOLUMN
    REPCAT$_REPGROUP_PRIVS
    REPCAT$_REPOBJECT
    REPCAT$_REPPROP
    REPCAT$_REPSCHEMA
    REPCAT$_RESOLUTION
    REPCAT$_RESOLUTION_METHOD
    REPCAT$_RESOLUTION_STATISTICS
    REPCAT$_RESOL_STATS_CONTROL
    REPCAT$_RUNTIME_PARMS
    REPCAT$_SITES_NEW
    REPCAT$_SITE_OBJECTS
    REPCAT$_SNAPGROUP
    REPCAT$_TEMPLATE_OBJECTS
    REPCAT$_TEMPLATE_PARMS
    REPCAT$_TEMPLATE_REFGROUPS
    REPCAT$_TEMPLATE_SITES
    REPCAT$_TEMPLATE_STATUS
    REPCAT$_TEMPLATE_TARGETS
    REPCAT$_TEMPLATE_TYPES
    REPCAT$_USER_AUTHORIZATIONS
    REPCAT$_USER_PARM_VALUES
    SQLPLUS_PRODUCT_PROFILE
    SYSCATALOG
    SYSFILES
    TAB
    TABQUOTAS
  )

  @exclude_tables = tables.map(&:downcase)
  @exclude_tables
end

.perform ⇒ IndexShotgun::Analyzer::Response

Search duplicate index

Returns:



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
 
# File 'lib/index_shotgun/analyzer.rb', line 22

def perform
  tables =
    ActiveSupport::Deprecation.silence do
      ActiveRecord::Base.connection.tables
    end
  tables.reject! { |table| exclude_tables.include?(table.downcase) }

  duplicate_indexes =
    tables.each_with_object([]) do |table, array|
      response = check_indexes(table)
      array.push(*response)
    end

  message =
    duplicate_indexes.each_with_object("") do |info, message|
      message << "        # =============================\n        # \#{info[:index].table}\n        # =============================\n\n        # \#{info[:result]}\n        # To remove this duplicate index, execute:\n        ALTER TABLE `\#{info[:index].table}` DROP INDEX `\#{info[:index].name}`;\n\n      EOS\n    end\n\n  total_index_count = tables.map { |table| table_indexes(table).count }.sum\n  message << <<-EOS.strip_heredoc\n    # ########################################################################\n    # Summary of indexes\n    # ########################################################################\n\n    # Total Duplicate Indexes  \#{duplicate_indexes.count}\n    # Total Indexes            \#{total_index_count}\n    # Total Tables             \#{tables.count}\n\n  EOS\n\n  response = Response.new\n  response.duplicate_index_count = duplicate_indexes.count\n  response.message               = message\n  response.total_index_count     = total_index_count\n  response.total_table_count     = tables.count\n\n  response\nend\n".strip_heredoc

.table_indexes(table) ⇒ Object

get indexes of table

Parameters:

  • table (String)

See Also:

  • [ActiveRecord[ActiveRecord::ConnectionAdapters[ActiveRecord::ConnectionAdapters::TableDefinition[ActiveRecord::ConnectionAdapters::TableDefinition#indexes]


98
99
100
 
# File 'lib/index_shotgun/analyzer.rb', line 98

def table_indexes(table)
  ActiveRecord::Base.connection.indexes(table)
end