RESTART 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

Restarts a previously halted index analysis started by an INITIATE INDEX ANALYSIS or RESTART INDEX ANALYSIS request that specified a checkpoint.

where:

 

Syntax element …

Specifies …

workload_name

Name of the workload on which index analysis is to be restarted.

QCD_name

Name of the query capture database in which workload_name is found.

index_name_tag

Name of the index analysis to be restarted.

A row with this name must exist in the QCD AnalysisLog or AnalysisStmts table or you cannot restart the index analysis.

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 the value is 0, Teradata Database ignores the CHECKPOINT clause.
  • If the value is greater than 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 restarted 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.

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

    The privileges required to submit a RESTART INDEX ANALYSIS request are the following:

     

    You must have this privilege set …

    On this table set in database QCD_name

    INSERT

  • IndexColumns
  • IndexRecommendations
  • INSERT
  • DELETE
  • AnalysisLog

    The user who performs a RESTART INDEX ANALYSIS request must be the same user who performed the checkpointed INITIATE INDEX ANALYSIS request.

    Normally invoked using the Teradata Index Wizard utility.

    The rules for using RESTART INDEX ANALYSIS and INITIATE INDEX ANALYSIS are the same. For more information, see “Rules for Performing INITIATE INDEX ANALYSIS” on page 565.

    If you do not specify a CHECKPOINT clause in your INITIATE INDEX ANALYSIS request, then you cannot restart a previously halted index analysis.

    If you are not satisfied with the outcome of a previously timed out INITIATE INDEX ANALYSIS request and want to extend the analysis with the intent of achieving better results, you can restart the analysis from the point at which it was stopped by specifying a CHECKPOINT rather than redoing the entire INITIATE INDEX ANALYSIS request from the beginning.

    RESTART INDEX ANALYSIS functions like INITIATE INDEX ANALYSIS, except that RESTART INDEX ANALYSIS begins its analysis with the first SQL request in the workload that was not checkpoint logged in the AnalysisLog table.

    If the restart aborts for any reason before completion, the next RESTART INDEX ANALYSIS request begins its analysis with the first SQL request in the workload that was not checkpointed in the AnalysisLog table.

    Once the restart completes successfully, index recommendations are written to the IndexRecommendations table within the specified QCD.

    You cannot specify a RESTART INDEX ANALYSIS request from a macro. If you execute a macro that contains a RESTART INDEX ANALYSIS request, Teradata Database aborts the request and returns an error.

    Assume that the query capture database named MyQCD exists on the system. The workload named MyWorkload in MyQCD consists of 100 SQL requests. The following SQL request causes the index analysis information to be saved after every 10 SQL requests because of its CHECKPOINT clause specification:

         INITIATE INDEX ANALYSIS ON tab1 
         FOR MyWorkload
         IN MyQCD AS table_1Index
         CHECKPOINT 10;

    Suppose a database restart occurs while processing the 55th SQL request. The system then records the information pertaining to the first 50 SQL requests processed.

    The following SQL request causes the analysis to be done from the 51st SQL request.

         RESTART INDEX ANALYSIS 
         FROM MyWorkload
         IN MyQCD AS table1_Index;

    Again assume that the query capture database MyQCD exists on the system and that the workload named MyWorkload consists of 100 SQL requests.

    The following SQL request does not specify a checkpoint, so no incremental information is written to the AnalysisLog table in MyQCD.

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

    Suppose once again that a database restart occurs while processing the 55th SQL request.

    The following SQL request returns an error because there is no row in AnalysisLog that checkpoints the specified index analysis name, table_1Index.

         RESTART INDEX ANALYSIS 
         FROM MyWorkload
         IN MyQCD AS table_1Index;
     
         *** Failure 5669 No restart information found for specified index      analysis ‘table_1Index'.

    For more information about index analysis, see:

  • “COLLECT DEMOGRAPHICS” on page 534
  • “COLLECT STATISTICS (QCD Form)” on page 537
  • “INITIATE INDEX ANALYSIS” on page 560
  • “INSERT EXPLAIN” on page 577
  • SQL Request and Transaction Processing
  • Teradata Index Wizard User Guide