Examples - Teradata Database

Example: Index Analysis

Assume that the QCD named MyQCD exists on the system. The following INITIATE INDEX ANALYSIS request performs successfully:

     INITIATE INDEX ANALYSIS ON table_1
     FOR MyWorkload
     IN MyQCD
     AS table_1Index;

If MyQCD does not exist in the system, the same request fails.

     INITIATE INDEX ANALYSIS ON table_1
     FROM MyWorkload
     IN MyQCD
     AS table_1Index;

     *** Failure 3802 Database 'MyQCD' does not exist.

Example: Index Analysis and Privileges

Assume that you have the INSERT privilege on the IndexRecommendations and IndexColumns tables or on database QCD_name. The following INITIATE INDEX ANALYSIS request performs successfully.

     INITIATE INDEX ANALYSIS ON table_1
     FOR MyWorkload
     IN MyQCD
     AS table_1Index;

If you do not have INSERT privilege on MyQCD, the same request fails.

     INITIATE INDEX ANALYSIS ON table_1
     FOR MyWorkload
     IN MyQCD
     AS table_1Index;

     *** Failure 3523 The user does not have INSERT access to
     MyQCD.IndexRecommendations.

Example: Index Analysis and Table List

When you specify a table list, then only that list of tables is considered for index analysis. If the workload does not have any tables matching at least one table in the list, then an error is reported. Otherwise, only the matching list of tables is considered for selecting the indexes.

Assume that MyWorkload describes the workload for tables table_1, table_2 and table_3. The following INITIATE INDEX ANALYSIS request performs successfully and the recommendations are considered only for table_1 and table_2 because table_3 is not specified in the table list.

     INITIATE INDEX ANALYSIS ON table_1, table_2
     FOR MyWorkload
     INTO MyQCD
     AS table_1Index;

Suppose MyWorkload describes the workload for table_1 and table_2 only. The following INITIATE INDEX ANALYSIS request fails because table_3 and table_4 are not defined in the workload.

     INITIATE INDEX ANALYSIS ON table_3, table_4
     FOR MyWorkload
     IN MyQCD
     AS table_1Index;

     *** Failure 5638 No match found for specified tables in the workload
     'MyWorkload'.

Example: Index Analysis and Duplicate Table Specifications

No error is reported if the same table is specified more than once for index selection. In the following example, table_1 is specified twice.

     INITIATE INDEX ANALYSIS ON table_1, table_2, table_1
     FOR MyWorkload
     IN MyQCD
     AS table_1Index;

Example: Index Analysis and Repeated Parameters

If index analysis parameters are specified, they must not be repeated. If the parameters are repeated, an error is reported.

In the following example, the only index analysis parameter specified, IndexesPerTable, is specified once.

     INITIATE INDEX ANALYSIS ON table_1
     FOR MyWorkload
     IN MyQCD
     AS table_1Index
     SET IndexesPerTable = 2;

In the following example, the index analysis parameter IndexesPerTable is specified twice, so an error is returned.

     INITIATE INDEX ANALYSIS ON table_1
     FOR MyWorkload
     IN MyQCD
     AS table_1Index
     SET IndexesPerTable=2, ChangeRate=4, IndexesPerTable=4;

     *** Failure 3706 Syntax error: Multiple "IndexesPerTable" options.

Note that the error is not caused by the discrepancy between the specified boundary conditions for the IndexesPerTable parameter: the same error would be reported if the boundary conditions matched in both specifications.

Example: Using a CHECKPOINT

You should specify the CHECKPOINT option for long operations and for workloads containing many SQL requests that must be analyzed. The checkpoint_trigger value indicates the frequency with which a checkpoint is taken. After every checkpoint_trigger number of SQL requests, a checkpoint is taken and completed IndexRecommendation information is saved in the AnalysisLog table in the QCD.

     INITIATE INDEX ANALYSIS ON table_1
     FOR MyWorkload
     IN MyQCD
     AS table_1Index
     SET IndexesPerTable = 2
     KEEP INDEX
     CHECKPOINT 10;

Example: Setting a TIME LIMIT on an Index Analysis

This example places a 10 minute time limit on the index analysis:

     INITIATE INDEX ANALYSIS
     FOR myworkload
     IN myqcd
     AS myfastidxanalysis
     TIME LIMIT = 10;

Example: Specifying a CHECKPOINT and a TIME LIMIT

This example takes a checkpoint for every request in the workload and places a 1 minute time limit on the index analysis.

     INITIATE INDEX ANALYSIS
     FOR wl1
     IN myqcd
     AS wl1_analysis1
     CHECKPOINT 1 
     TIME LIMIT = 1;

Example: Include NUSI and Simple Join Indexes Only in the Analysis

This example considers only NUSIs and simple single-table join indexes for the index analysis.

     INITIATE INDEX ANALYSIS ON tab1
     FOR MyWorkload
     IN MyQCD AS tab1Index
     WITH INDEX TYPE 4, 5;

Example: Exclude All Join Indexes From The Analysis

This example excludes all types of join indexes from the index analysis.

     INITIATE INDEX ANALYSIS ON tab1
     FOR MyWorkload
     IN MyQCD AS tab1Index
     WITH NO INDEX TYPE 5, 6;

Example: Include All Valid Index Types in the Analysis

This example considers all valid index types for the index analysis by default.

     INITIATE INDEX ANALYSIS ON tab1
     FOR MyWorkload
     IN MyQCD AS tab1Index;