INITIATE INDEX ANALYSIS - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
15.10
Language
English (United States)
Last Update
2018-10-06
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

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

where:

 

Syntax element …

Specifies …

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
  • See “Boundary Options” on page 564 for the definitions and maximum bounds for these 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 (JIAGG).

    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, Teradata Database ignores the CHECKPOINT clause.
  • If checkpoint_trigger is > the total number of queries to be analyzed, Teradata Database does not take a checkpoint snapshot.
  • Also see “Example 6: Using a CHECKPOINT” on page 570.

    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

    Note that 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 7: Setting a TIME LIMIT on an Index Analysis” on page 571.

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

    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.

    Normally invoked using the Teradata Index Wizard utility.

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

     

                        Option

                                                     Definition

    CHANGERATE

    Every column appearing in an UPDATE SET clause is automatically assigned a change rating ranging from 0 to 9, representing the frequency at which it is updated. For more information about change ratings, see Database Design.

    The system assigns columns with a very high update frequency a change rating of 9, while columns that are never updated are assigned a change rating of 0.

    Columns with a change rating greater then this user‑specified ChangeRate parameter are not considered during index analysis for potential indexes. In other words, the ChangeRate value is a threshold for determining whether to use columns for an index analysis or not.

    If the specified ChangeRate parameter is 9, the change rating system is not used to disqualify columns from index analysis.

    The default is 5.

    The valid range is 0 to 9, where 0 means the column is not updated and 9 means the column is highly volatile with respect to the specified workload.

    COLUMNSPERINDEX

    Maximum number of composite secondary index columns to be considered for index recommendation by the Index Wizard.

    The default is 4.

    The valid range is 1 to 64.

    COLUMNSPERJOININDEX

    Maximum number of referenced columns in a single‑table join index to be considered for index analysis by the Index Wizard.

    The default is 8.

    The valid range is 1 to 16.

    INDEXMAINTMODE

    Controls how estimated index maintenance costs are used during analysis. The following values are supported.

  • 0
  • Maintenance costs are not estimated.

    The feature is disabled.

  • 1
  • Maintenance costs are estimated only as supplemental information for the final index recommendations.

    This is the default.

  • 2
  • Maintenance costs are used to evaluate and choose between candidate indexes and are also included with the final recommendations.

    INDEXESPERTABLE

    Maximum number of new indexes, including secondary and join indexes, that can be recommended per table.

    The default is 16.

    The system limit is 32. Each value‑ordered secondary index reduces the number of consecutive indexes by 2.

    The valid range is 1 to 32.

    SEARCHSPACE

    Maximum size of the search space to be allowed for evaluating index candidates. The higher the number you specify, the more candidate indexes the system can evaluate.

    The number specified for SEARCHSPACE corresponds to an internal variable that is used to control the size of the search space evaluated by the index search algorithm.

    Keep in mind that the larger the number you specify, the longer the evaluation time. To shorten your evaluation times, change the specification for SearchSpace to a lower number.

    The default is 256.

    The valid range is 128 to 512.

    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.

    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.

    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).

    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” on page 347, “MERGE” on page 405, and CREATE ERROR TABLE” in SQL Data Definition Language.

    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” on page 590.

    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” on page 590.

    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 (see “INITIATE PARTITION ANALYSIS” on page 573) 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.

    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.

     

    Change Rate Rank

    Description

     

    Percentage of Updates on Column

    0

    No updates on the column.

    0

    1

    1 update per 1,000,000 selects on the table.

    .0001

    2

    1 update per 100,000 selects on the table.

    .001

    3

    1 update per 10,000 selects on the table.

    .01

    4

    1 update per 1,000 selects on the table.

    .1

    5

    1 update per 100 selects on the table.

    1

    6

    5 updates per 100 selects on the table.

    5

    7

    10 updates per 100 selects on the table.

    10

    8

    25 updates per 100 selects on the table.

    25

    9

    More than 25 updates per 100 selects on the table.

    >25

    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.

    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.

    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.

    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'.

    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;

    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.

    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;

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

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

    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;

    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;

    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;

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

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

    For more information about QCDs and index analysis, see:

  • “COLLECT DEMOGRAPHICS” on page 534
  • “COLLECT STATISTICS (QCD Form)” on page 537
  • “INITIATE PARTITION ANALYSIS” on page 573
  • “RESTART INDEX ANALYSIS” on page 590
  • “INSERT EXPLAIN” on page 577
  • “CREATE TABLE (Index Definition Clause)” in SQL Data Definition Language
  • SQL Request and Transaction Processing
  • Database Design
  • Teradata Index Wizard User Guide