INITIATE INDEX ANALYSIS - Teradata Database

SQL Data Manipulation Language
Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
psg1480972718197.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

Purpose

Analyzes a query workload and generates a set of recommended secondary and single-table join indexes for optimizing its processing.

For more information about QCDs and index analysis, see:

Required Privileges

The privileges required to submit an INITIATE INDEX ANALYSIS request are the following:

You must have this privilege set … On this table set in database QCD_name…
  • INSERT

     and

  • SELECT
 IndexRecommendations
INSERT
  • AnalysisStmts
  • IndexMaintenance
  • IndexColumns
  • JoinIndexColumns

If you specify a checkpoint trigger, you must have both INSERT and DELETE privileges on the AnalysisLog table in QCD_name.

Syntax



Syntax Elements

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.
workload_name
Name of the workload to which the queries to be analyzed belong.
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.
index_name_tag
Name to be assigned to the index recommendations within QCD_name.
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.
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.
This option permits you to perform speculative “what if” analyses on the data when you want to anticipate how the Optimizer will handle various situations you anticipate might occur.
WITH INDEX TYPE number
WITH NO INDEX TYPE number
Types of secondary and single-table join indexes that are to be considered for analysis by the Teradata Index Wizard.
  • If you specify the clause as WITH INDEX TYPE number, the Teradata Index Wizard includes the specified set of index types in its analysis.
  • If you specify the clause as WITH NO INDEX TYPE number, the Teradata Index Wizard excludes the specified set of index types from its analysis.
  • If you do not specify this clause, the Teradata Index Wizard includes all valid index types in its analysis by default.
The number code is an identifier for the IndexType column values stored in the QCD IndexRecommendations table.
The valid options for number and what they specify are as follows.
  • 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).

Note that the Teradata Index Wizard includes only single-table join indexes in its analyses for codes 5 and 6 and that hash indexes are not supported.
See SQL Request and Transaction Processing for more information about the QCD and the Teradata Index Wizard. Also see Teradata Index Wizard User Guide for information about using the Teradata Index Wizard client utility.
CHECKPOINT checkpoint_trigger
Number of queries after which a checkpoint snapshot must be taken.
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.
Also see Example: Using a CHECKPOINT..
TIME LIMIT = elapsed_time
The maximum elapsed time in minutes that this index analysis should take 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 to see if the specified time limit has been exceeded.
Also see Example: Setting a TIME LIMIT on an Index Analysis..

ANSI Compliance

INITIATE INDEX ANALYSIS is a Teradata extension to the ANSI SQL:2011 standard.