Usage Notes - Teradata Database

Invocation

Normally invoked using the Teradata Index Wizard utility.

Boundary Options

The following table lists the valid boundary option values and their definitions.

Rules for Performing INITIATE INDEX ANALYSIS

The following set of rules applies to INITIATE INDEX ANALYSIS:

• The analysis accepts any of the following DML statements as valid workload components:

  DELETE INSERT

  MERGE SELECT

  UPDATE

• The specified QCD database must exist in the system. Otherwise, the request aborts and the system returns an error.
• The specified workload must exist in the specified QCD_name database. Otherwise, the request aborts and the system returns an error.
• If you specify a table list, then only that list of tables is considered for index analysis.

  If the workload does not contain any tables that match at least one table in the table list, then an error is reported. Otherwise, only the matching list of tables is considered for index analysis.

• A maximum of 1,024 tables can be analyzed per workload.
• No error is reported if the same table is specified more than once for index analysis.
• The index analysis parameters must not be repeated.

  If they are repeated, or if a value is out of range for a parameter, then the request aborts and the system returns an error.

• If the session is logged off or restarted while INITIATE INDEX ANALYSIS is being performed and no checkpoint is specified, then no index recommendations are saved.

  You must perform the INITIATE INDEX ANALYSIS request again in the restarted session to create the index recommendations.

Sequence of Events for an Index Analysis

The following process indicates the general phases of an index analysis:

1. The analysis begins when an INITIATE INDEX ANALYSIS request is submitted.
2. The database query analysis tool analyzes the submitted workload for indexing opportunities.
3. The index recommendations are written to the IndexRecommendations, IndexColumns, and JoinIndexColumns tables of the specified QCD database.
4. The recommendations are retrieved for evaluation using a set of SQL SELECT requests defined within a number of macros provided within each QCD.

   For example, the following SELECT request is the core of the Recommendations macro.

        SELECT TableName, DatabaseName, IndexTypeText, SpaceEstimate,             IndexDDL
        FROM IndexRecommendations
        WHERE WorkloadID = :WorkloadID;

   You can find a list of the index recommendation query macros in SQL Request and Transaction Processing .

Retrieving a Completed Index Analysis

After the index analysis completes successfully, you can retrieve the index recommendations from the IndexRecommendations and IndexColumns tables in the appropriate QCD (see SQL Request and Transaction Processing for more information about the QCD).

Using the WITH INDEX TYPE or WITH NO INDEX TYPE Option

You can use this option to either include or exclude various types of secondary and join indexes from consideration for the analysis to be performed on the QCD data by the Teradata Index Wizard.

You can include for consideration, or exclude from consideration, by the analysis any of the following types of indexes.

• Unique secondary (USI)
• Nonunique secondary (NUSI)
• Hash-ordered secondary
• Value-ordered secondary
• Single-table simple join
• Single-table aggregate join

The default, indicated by not specifying this clause in your INITIATE INDEX ANALYSIS request, is to include all valid index types for consideration by the analysis. Note that the list of valid index types for index analysis does not include multitable join indexes or hash indexes.

For example, you might join indexes from consideration by the analysis, which you could consider to be undesirable for workloads that invoke utilities such as Teradata Archive/Recovery or MultiLoad, neither of which can be run against tables that have join indexes defined on them. For more information about restrictions and interactions with tables on which join indexes are defined, see “CREATE JOIN INDEX” in SQL Data Definition Language and Database Design .

In this case, you can substitute FastLoad for MultiLoad to batch load rows into an empty staging table and then use either an INSERT … SELECT request with error logging or a MERGE request with error logging to move the rows into their target table if the scenario permits. For details, see INSERT/INSERT … SELECT, MERGE, and CREATE ERROR TABLE” in SQL Data Definition Language .

Using the CHECKPOINT Option

For long index analyses or cases where the workload has many SQL requests that must be analyzed, you should always specify a checkpoint using the CHECKPOINT option.

The checkpoint trigger value indicates the frequency with which a checkpoint is logged in the AnalysisLog table. A checkpoint is taken after every n request occurrences, where n is the checkpoint trigger value, and the interim index analysis information is logged.

Note that you can specify both the CHECKPOINT and the TIME LIMIT options for an INITIATE INDEX ANALYSIS request.

For information about how to restart a checkpointed index analysis, see RESTART INDEX ANALYSIS.

CHECKPOINT and TIME LIMIT Options

The intent of the CHECKPOINT option and the related RESTART INDEX ANALYSIS statement is to establish safeguards for long running INITIATE INDEX ANALYSIS requests for cases where you want to ensure that you do not have to restart the analysis from the beginning if the request unexpectedly aborts. See RESTART INDEX ANALYSIS.

The TIMELIMIT option permits you to place a time limit on the index analysis. Note that you must be willing to accept the best recommendations found up to that point if you specify this option.

Depending on the size and complexity of the defined workload, the operations undertaken by an INITIATE INDEX ANALYSIS request can be very resource-intensive. The process of repeatedly calling the Optimizer with different sets of candidate indexes incurs heavy CPU usage in the Parsing Engine. You should not use this statement on a production system.

INITIATE PARTITION ANALYSIS and INITIATE INDEX ANALYSIS are related statements in the sense that both first examine a workload and then make recommendations to improve performance. You can submit one or both of these statements in either order for a given workload.

Evaluation of the ChangeRate Variable by INITIATE INDEX ANALYSIS

The following table indicates the method INITIATE INDEX ANALYSIS uses to evaluate column change rate values for index recommendation. Only those columns with a change rate rank ≤  the specified change rate rank are considered for recommendation as an index or composite index component. If no change rate is specified, the default value is 5.

INITIATE INDEX ANALYSIS Not Supported From Macros

You cannot specify an INITIATE INDEX ANALYSIS request from a macro. If you execute a macro that contains an INITIATE INDEX ANALYSIS request, the system returns an error.