15.10 - Teradata Index Wizard Overview - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

The Teradata Index Wizard is used to re‑engineer or redesign an existing database by analyzing specific SQL workloads for opportunities to optimize performance through appropriate secondary index and partitioning expression usage. The Teradata Index Wizard recommends only partitioning expressions based on RANGE_N functions.

The analysis can produce recommendations to delete, as well as to add, indexes to the existing physical design. No drop recommendations are made for existing partitioning expressions.

The Index Wizard works in two modes.

  • Wizard mode
  • In Wizard mode, the Index Wizard provides a menu‑driven interface to guide you through the steps of identifying a workload, and then identifying and implementing a set of indexes that should help to improve the performance of that workload. See “General Procedure for Using the Teradata Index Wizard to Optimize Index and Partitioning Expression Selection” on page 830 for details of the processes that wizard mode provides.

  • What If? mode
  • In What If? mode, the Index Wizard allows you to provide a set of recommended indexes or partitioning expressions. The Index Wizard then simulates the set of indexes you submit and produces a report of the estimated overall improvement to the workload if the proposed indexes, or partitioning expressions, or both, had been implemented.

    The Index Wizard also provides query-by-query details indicating the improvement for each individual query in a defined workload, and whether or not your simulated indexes were used by the Optimizer to develop the new query plan. The limit on query text size the Index Wizard can process is 1 megabyte. Overflow text is stored in the table qcd.QryRelX (see “QryRelX” on page 785), where qcd represents the name you have assigned to the containing QCD database.

    See Teradata Index Wizard User Guide for details.

    The logic for evaluating and selecting indexes and partitioning expressions is located in the Optimizer. The architecture for the Index Wizard consists of the SQL Optimizer (see Chapter 2: “Query Rewrite and Optimization”) and additional code to generate and simulate candidate indexes.

    The approach used for index selection is let the Optimizer choose the indexes it “wants” to use, meaning those indexes that it first searches for when presented with an SQL request to optimize. The index analysis process calls the Optimizer repeatedly with different sets of simulated candidate indexes. The analysis takes promising columns from the Optimizer's analysis of WHERE conditions done by the Optimizer and uses them as the initial input to the search phase of the process.

    The analysis then uses a combinatorial search engine to explore the solution space, which consists of various combinations of the promising columns as well as other design factors:

  • In the case of secondary indexes, the design factors include different combinations of columns that make up a composite index, the use of extra columns to cover queries, and different types of secondary indexes such as value‑ordered indexes in addition to the more standard hashed indexes.
  • In the case of join indexes, the primary design factors include different combinations of columns that make up the primary index definition for the join index, the use of additional columns to cover queries, and the use of the ROWID to define non‑covering join indexes.
  • In the case of partitioning expressions, the primary design factor explored is the optimal level of granularity for each candidate partitioning column.
  • The Index Wizard only recommends single‑level partitionings with partitioning expressions based on RANGE_N functions, but you can infer recommendations for multilevel partitionings from any report that recommends multiple partitioning expressions, then experiment with combining some or all of those recommendations to create a multilevel partitioning.

    The conclusion to draw from this is that the Index Wizard is not merely an automated version of physical database design rules that were previously performed manually. The selection process considers a large number of candidates, and calls the Optimizer to evaluate and rank each candidate index by its effect on the estimated query cost. This approach ensures consistency with the choice of indexes made by the Optimizer while avoiding duplication of the Optimizer cost model within the design tool.

    The major architectural components of the Index Wizard are the following four modules:

     

          Component

                                                             Description

    Query analyzer

    Forms a list of interesting or indexable columns by calling the predicate analysis code of the SQL Optimizer.

    Examines the projection list and other parts of a query for covering information.

    Index search engine

    Combinatorial search algorithm capable of performing a random search on a large solution space of candidate indexes and partitioning expressions.

    Index simulator

    Simulates indexes during the query optimization phase of index and partitioning expression analysis.

    Query optimizer

    The system SQL Optimizer.

    Index and partitioning expression analysis uses its estimated plan costs to evaluate candidate indexes.

    Note that the Partition Analysis system uses the same architectural components as the Index Analysis system.

    See Teradata Index Wizard User Guide for details about the various Teradata Index Wizard processes.

    The following table outlines the general process followed in performing index and partitioning expression analysis in wizard mode using the Teradata Index Wizard:

     

    Stage

            Activity Name

                                             Activity Description

    1

    Identify a workload.

    Identify a set of SQL requests that constitutes a workload for consideration for analysis.

    The following repositories are potential sources for identifying workload components:

  • Database query log (see SQL Data Definition Language and Database Administration).
  • Query capture database (see Chapter 7: “Query Capture Facility”).
  • See “Workload Identification” on page 834 for details.

    2

    Define the workload.

    Perform the AddWorkload macro to define a new workload in a query capture database.

    See “Workload Definition” on page 836 for details.

    Also see “INSERT EXPLAIN” in SQL Data Manipulation Language.

    3

    Analyze indexes.

    Perform an index analysis on the defined workload to produce a set of index recommendations.

    See “Index and Partitioning Expression Analysis” on page 839 for details.

    Also see “INITIATE INDEX ANALYSIS” and “INITIATE PARTITION ANALYSIS” in SQL Data Manipulation Language. INITIATE INDEX ANALYSIS and RESTART INDEX ANALYSIS are not supported for geospatial NUSIs.

    4

    Report the analysis.

    View summary and detailed information about the workload, the existing physical design, the changes proposed by the Index Wizard, and the costs versus benefits of the index and partitioning expression recommendations relative to the workload.

    5

    Validate the indexes.

    Validate the recommended indexes on your production system or on a test system with the aid of the Teradata System Emulation Tool (see Teradata System Emulation Tool User Guide for details).

    This is an optional step in the process.

    See “Index and Partitioning Expression Validation” on page 845 and “DIAGNOSTIC “Validate Index”” in SQL Data Manipulation Language for details.

    6

    Implement Recommendations.

    Apply the index recommendations to your production system.

    This means to create or drop recommended indexes and to create recommended partitioning expressions at your discretion.

    This is an optional step in the process.

    See “Applying the Index and Partitioning Expression Recommendations” on page 849 for details.

    Also see the documentation for the following SQL statements in SQL Data Definition Language:

  • ALTER TABLE
  • CREATE INDEX
  • CREATE JOIN INDEX
  • DROP INDEX
  • The following constraints and restrictions apply to the use of the Index Wizard:

  • By default, any query search conditions that default to full table scans cannot benefit from indexes; therefore, the Teradata Index Wizard does not generate index recommendations for such queries.
  • If you generate index recommendations on a test system without emulating the production environment, then the index recommendations might not produce an optimal result when applied to the production system.
  • You should always use Target Level Emulation (see Database Administration) to emulate production environments when performing index analyses on a test system.

  • The Teradata Index Wizard produces recommendations for partitioning expressions (the Teradata Index Wizard recommends only partitioning expressions based on RANGE_N functions, secondary indexes, and single‑table join indexes only.
  • a A workload is identified. See “Workload Identification” on page 834.

    b Query plans that characterize the workload are collected in the QCD. This is referred to as defining the workload. See “Workload Definition” on page 836.

    c Index analysis and validation are performed on the QCD data. See “Index and Partitioning Expression Analysis” on page 839 and “Index and Partitioning Expression Validation” on page 845.

    i The Teradata Index Wizard begins the analysis by invoking either the SQL INITIATE INDEX ANALYSIS statement or the INITIATE PARTITION ANALYSIS statement (see “INITIATE INDEX ANALYSIS” and “INITIATE PARTITION ANALYSIS” in SQL Data Manipulation Language). INITIATE INDEX ANALYSIS is not valid for geospatial NUSIs.

    i The Teradata Index Wizard begins the analysis by invoking either the SQL INITIATE INDEX ANALYSIS statement or the INITIATE PARTITION ANALYSIS statement (see “INITIATE INDEX ANALYSIS” and “INITIATE PARTITION ANALYSIS” in SQL Data Manipulation Language). INITIATE INDEX ANALYSIS is not valid for geospatial NUSIs.

    ii INITIATE INDEX ANALYSIS selects candidate indexes for the submitted workload and submits each of them with the workload to the Optimizer. INITIATE INDEX ANALYSIS is not valid for geospatial NUSIs.

    Note that you can restart timed out index analyses using appropriate CHECKPOINT and TIMELIMIT option specifications for the INITIATE INDEX ANALYSIS/RESTART INDEX ANALYSIS statements (see “INITIATE INDEX ANALYSIS” and “RESTART INDEX ANALYSIS” in SQL Data Manipulation Language for details) or Teradata Index Wizard (see Teradata Index Wizard User Guide).

     

    IF the optimizer generates a query plan that …

    THEN the Teradata Index Wizard …

    does not use the candidate index

    drops that index from consideration as a recommendation.

    uses the candidate index

    keeps that index for recommendation.

    Alternatively, INITIATE PARTITION ANALYSIS selects candidate partitioning expressions for tables in the submitted workload and submits each of them with the workload to the Optimizer. INITIATE INDEX ANALYSIS is not valid for geospatial NUSIs.

    See “Analyzing Indexes and Partitioning Expressions” on page 839 for details of this process.