15.10 - Index and Partitioning Expression Analysis - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
Programming Reference
User Guide

The objective of index analysis (including Partitioning Expression Analysis) is to recommend a set of new indexes and partitioning expressions using CREATE INDEX, CREATE JOIN INDEX, or ALTER TABLE … MODIFY PRIMARY INDEX statements on the different tables referenced in the workload.

In the case of secondary and join indexes, index analysis also provides a set of existing indexes that it recommends be dropped using DROP INDEX requests when the analysis supports removing those indexes.

See Teradata Index Wizard User Guide for details about the objectives of index and partitioning expression analysis and how to set those analyses up to obtain optimal results.

Note: Partitioning expression analysis does not recommend dropping existing partitioning expressions because it assumes that they might be important to the performance of the Archive/Recovery client utility and the MultiLoad insert and delete operations.

See Teradata Archive/Recovery Utility Reference and Teradata MultiLoad Reference for details about how to use those utilities.

Depending on the size and complexity of the defined workload, index and partition analysis operations can be resource intensive, particularly because the repeated calls to the Optimizer with different sets of candidate indexes or partitioning expressions (or both) incurs heavy CPU usage. Because of this, you should generally avoid performing Index Analysis operations (meaning the INITIATE INDEX ANALYSIS and INITIATE PARTITION ANALYSIS statements) on a production system.

See Teradata Index Wizard User Guide for details about this process.

The following stages describe the process used by the Index Wizard to perform an index analysis:

1 The candidate search space and proposed index list for the workload are set null.

2 The workload candidate search space is built as follows:

a For each SQL request in the workload.

1      Regenerate the query plan.

During this process, the Predicate Analyzer is invoked to build the statement candidate index list using the Candidate Index Enumerator.

2      Invoke the Index Search Engine to filter candidate indexes.

To perform this task, the Index Search Engine analyzes the statement index candidate list and removes the least promising indexes (including recommendations that would increase update costs to the base table or index subtables), producing the resultant statement proposed index list.

3      Add the statement proposed index list to the workload candidate search space.

4       Determine if any table constraints have been violated.


IF a table constraint violation is …



1 Invoke the Index Search Engine to filter candidate indexes from the workload candidate search space.

2 Build a new proposed workload index list.

3 Invoke the Query Cost Analyzer to regenerate the query plan and compute costs by simulating the indexes identified as candidates by the Index Search Engine.

Go to Stage 2.a.4.

not found

Go to Stage 2.a.2.

5      Set the workload candidate search space to the workload proposed index list.

b Act on any checkpoints that have been set for the index analysis.


IF a checkpoint is …

AND the specified checkpoint frequency …



is hit

write the proposed index list for the workload to the QCD AnalysisLog table.

not specified


go to Stage 3.0.0.

3 Filter the proposed index list for the workload using the best indexes analyzed to this point to produce the index recommendations.

4 Write the recommendations to the QCD IndexRecommendations and PartitionRecommendations tables (see “IndexRecommendations” on page 770 and “PartitionRecommendations” on page 779.

5 Delete any remaining AnalysisLog table entries.

You can specify various index analysis parameters to control the choice of candidate indexes using the various boundary options of the INITIATE INDEX ANALYSIS and INITIATE PARTITION ANALYSIS statements (see SQL Data Manipulation Language for details) or the Teradata Index Wizard (see Teradata Index Wizard User Guide). These boundary options only affect the index analysis phase of database query analysis, however, and not the query plan generated by the Optimizer.

Note: INITIATE INDEX ANALYSIS is not valid for geospatial NUSIs.

Note that index recommendations are not restricted to adding recommended new indexes: they can also include recommendations to drop existing indexes.

Note, too, that index recommendations never include volatile tables that are accessed by the SQL requests making up the workloads.

The process of analyzing statistical data in order to develop a set of recommended indexes is indicated by the following graphic.

Note that if the analysis is performed on a production system, the two Get Index Recommendations stages collapse into a single stage.