INITIATE INDEX ANALYSIS
[ ON on_spec [,...] ]
FOR workload_name IN QCD_name AS index_name_tag
[ SET set_spec [,...] ]
[ KEEP INDEX ]
[ USE MODIFIED { STATISTICS | STATS | STAT } ]
WITH [NO] INDEX TYPE number [,...]
[ CHECKPOINT checkpoint_trigger ]
[ TIME LIMIT = elapsed_time ] [;]
Syntax Elements
- on_spec
[ database_name. | user_name. ] table_name
- workload_name
- Name of the workload to which the queries to be analyzed belong.
- index_name_tag
- Name to be assigned to the index recommendations within QCD_name.
- QCD_name
- QCD workload database in which workload_name exists.
- INITIATE INDEX ANALYSIS stores the index recommendations for the specified workload in this QCD database.
- set_spec
boundary_option = value
- KEEP INDEX
- Index recommendations are not to contain any DROP INDEX or DROP STATISTICS recommendations.
- The default is to recommend DROP INDEX and DROP STATISTICS recommendations when the analysis indicates their usefulness.
- USE MODIFIED STATISTICS
- Perform the index analysis with the user-modified statistics stored in the ModifiedStats column of QCD table TableStatistics rather than the statistics gathered with COLLECT STATISTICS (QCD Form).
- If the ModifiedStats column is not populated, then the statistics stored in StatisticsInfo are used instead and the result is the same as if you had not specified USE MODIFIED STATISTICS.
- You can perform "what if" analyses on the data to examine how the Optimizer processes different scenarios.
- WITH INDEX TYPE number
- WITH NO INDEX TYPE number
- Types of secondary and single-table join indexes that are to be considered for analysis.
- If you specify the clause as WITH INDEX TYPE number, the specified set of index types in its analysis is included.
- If you specify the clause as WITH NO INDEX TYPE number, the specified set of index types from its analysis is excluded.
- If you do not specify this clause, all valid index types are included in its analysis by default.
- The number code is an identifier for the IndexType column values stored in the QCD IndexRecommendations table.
- Following are the values you can specify for number and the corresponding descriptions.
| Option |
Description |
|---|
| 1 |
Unique secondary index (USI). |
| 2 |
Value-ordered secondary index (VOSI). |
| 3 |
Hash-ordered secondary index (HOSI). |
| 4 |
Nonunique secondary index (NUSI). |
| 5 |
Simple join index (JI). |
| 6 |
Aggregate join index (AJI). |
- See Query Capture Facility for more information about the QCD.
- CHECKPOINT checkpoint_trigger
- Number of queries after which to take a checkpoint snapshot.
- The value for checkpoint_trigger must be a positive integer.
- If checkpoint_trigger is 0, the system ignores the CHECKPOINT clause.
- If checkpoint_trigger is > the total number of queries to be analyzed, the system does not take a checkpoint snapshot.
- See Example: Using a CHECKPOINT.
- TIME LIMIT = elapsed_time
- The maximum elapsed time in minutes allowed for this index analysis to complete.
- The default value is no time limit.
- The permitted range of specified values is from 1 to 2880 minutes, for a maximum of 48 hours.
- You must specify the value for elapsed_time as an integer.
- If the index analysis does not complete before reaching the specified time limit, the system stops the task and retains the best recommendations found before the time limit expired.
- The time limit that you specify is only an approximation because the ongoing index analysis task only checks periodically as to whether the specified time limit has been exceeded.
- Also see Example: Setting a TIME LIMIT on an Index Analysis.
- database_name
- user_name
- Containing database or user for table_name if something other than the current database or user.
- table_name
- Table set to be analyzed for index recommendations.
- If no table is specified, then all tables referenced in the specified workload_name are analyzed for index recommendations.
- This option permits you to implicitly instruct INITIATE INDEX ANALYSIS not to recommend indexes on certain tables.
- boundary_option = value
- Clause that sets upper bounds on the specified option.
- The following options and maximum bounds are valid specifications.
- CHANGERATE
- COLUMNSPERINDEX
- COLUMNSPERJOININDEX
- INDEXMAINTMODE
- INDEXESPERTABLE
- SEARCHSPACE
- For the definitions and maximum bounds for these options, see Boundary Options.