INITIATE INDEX ANALYSIS Syntax | SQL Statements | Teradata Vantage - INITIATE INDEX ANALYSIS Syntax - Analytics Database - Teradata Vantage

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 various 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 Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142 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 up to the point when 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.