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:
- COLLECT DEMOGRAPHICS
- COLLECT STATISTICS (QCD Form)
- INITIATE PARTITION ANALYSIS
- RESTART INDEX ANALYSIS
- INSERT EXPLAIN
- “CREATE TABLE (Index Definition Clause)” in Teradata Vantage™ SQL Data Definition Language Detailed Topics , B035-1184
- Teradata Vantage™ SQL Request and Transaction Processing, B035-1142
- Teradata Vantage™ - Database Design, B035-1094
- Teradata® Index Wizard User Guide, B035-2506
Required Privileges
The privileges required to submit an INITIATE INDEX ANALYSIS request are the following:
Privilege Required | Table Set in Database QCD_name |
---|---|
and
|
IndexRecommendations |
INSERT |
|
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.
- workload_name
- Name of the workload to which the queries to be analyzed belong.
- QCD_name
- QCD workload database in which workload_name exists.
- 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.
- KEEP INDEX
- Index recommendations are not to contain any DROP INDEX or DROP STATISTICS recommendations.
- 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).
- 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.
- CHECKPOINT checkpoint_trigger
- Number of queries after which to take a checkpoint snapshot.
- TIME LIMIT = elapsed_time
- The maximum elapsed time in minutes allowed for this index analysis to complete.
ANSI Compliance
INITIATE INDEX ANALYSIS is a Teradata extension to the ANSI SQL:2011 standard.