Teradata Index Wizard analyzes SQL queries and suggests candidate indexes to enhance their performance.
The workload definitions, supporting statistical and demographic data, and index recommendations are stored in various QCD tables.
Using data from a QCD or the Database Query Log (DBQL), Teradata Index Wizard:
- Recommends, using an INITIATE PARTITION ANALYSIS statement, the potential performance benefits from adding a partitioning expression to one or more tables in a given workload.
The statement does not recommend the complete removal of any defined partitioning expressions. It considers, however, the alteration of an existing partitioning expression if a Partitioned Primary Index (PPI) table is explicitly included in the table_list.
- Recommends secondary indexes for the tables based on workload details, including data demographics, that are captured using the QCF.
- Enables you to validate index recommendations before implementing the new indexes.
- Enables you to perform what-if analysis on the workload. Teradata Index Wizard allows you to determine whether your recommendations actually improve query performance.
- Interfaces with other Teradata Tools and Utilities, such as Teradata SET to perform offline query analysis by importing the workload of a production system to a test system
- Uses Teradata Visual Explain and Compare tools to provide a comparison of the query plans with and without the index recommendations.
Teradata Index Wizard can be started from Teradata Visual Explain and Teradata SET. Teradata Index Wizard can also open these applications to help in your evaluation of recommended indexes.
Demographics
Teradata Index Wizard needs demographic information to perform index analysis and to make recommendations. You can collect the following types of data demographics using SQL:
Teradata Index Wizard needs demographic information to perform index analysis and to make recommendations. You can collect the following types of data demographics using SQL:
- Query demographics
Use the INSERT EXPLAIN statement with the WITH STATISTICS and DEMOGRAPHICS clauses to collect table cardinality and column statistics.
- Table demographics
Use the COLLECT DEMOGRAPHICS statement to collect the row count and the average row size in each of the subtables in each AMP on the system.