Module: IndexShotgun::Analyzer

Defined in:
lib/index_shotgun/analyzer.rb

Overview

rubocop:disable Metrics/ModuleLength

Defined Under Namespace

Classes: Response

Constant Summary collapse

ORACLE_SYSTEM_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
].freeze

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
94
95
 
# 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)

    next if source_index.unique && target_index.unique

    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 



280
281
282
283
284
285
286
287
288
289
290
291
 
# File 'lib/index_shotgun/analyzer.rb', line 280

def exclude_tables
  return @exclude_tables if @exclude_tables

  # Rails default tables
  tables = %w[ar_internal_metadata schema_migrations]

  # Oracle system tables
  tables += ORACLE_SYSTEM_TABLES

  @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 =
    silence_deprecations 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, str|
      str << <<~MSG
        # =============================
        # #{info[:index].table}
        # =============================

        # #{info[:result]}
        # To remove this duplicate index, execute:
        ALTER TABLE `#{info[:index].table}` DROP INDEX `#{info[:index].name}`;

      MSG
    end

  total_index_count = tables.map {|table| table_indexes(table).count }.sum
  message << <<~MSG
    # ########################################################################
    # Summary of indexes
    # ########################################################################

    # Total Duplicate Indexes  #{duplicate_indexes.count}
    # Total Indexes            #{total_index_count}
    # Total Tables             #{tables.count}

  MSG

  response = Response.new
  response.duplicate_index_count = duplicate_indexes.count
  response.message               = message
  response.total_index_count     = total_index_count
  response.total_table_count     = tables.count

  response
end

.silence_deprecations ⇒ Object 



293
294
295
296
297
298
299
300
301
302
303
 
# File 'lib/index_shotgun/analyzer.rb', line 293

def silence_deprecations
  if ActiveSupport.version >= Gem::Version.create("7.1.0")
    ActiveSupport::Deprecation::Deprecators.new.silence do
      yield
    end
  else
    ActiveSupport::Deprecation.silence do
      yield
    end
  end
end

.table_indexes(table) ⇒ Object

get indexes of table

Parameters:

  • table (String) 


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

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