15.10 - Chapter 8 The Teradata Index Wizard - 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

This chapter describes the Teradata Index Wizard utility.

The Teradata Index Wizard helps you to re‑engineer existing databases by providing a method to propose optimal sets of partitioning expressions (the Teradata Index Wizard recommends only partitioning expressions based on RANGE_N functions, secondary indexes, and single‑table join indexes (the Teradata Index Wizard does not make recommendations for column partitioning, sparse or multitable join indexes, or for hash indexes, nor does it recommend dropping existing partitioning expressions) as well as partitioning expressions to support particular SQL query workloads. This process permits you to analyze the potential performance enhancing effects of various join and secondary indexes and partitioning expressions on statistical representations of live data from your data warehouse.

In most cases, the data was probably not available at the time you performed the physical design for the database, so the effects of the join and secondary indexes you defined to support your query workloads were guessed at rather than tested empirically.

In general, the processes described in this chapter apply equally well to row partition analysis, which uses the same software systems to generate recommended partitioning expressions, which it also stores in your QCD (see Chapter 7: “Query Capture Facility” for a description of the relevant partitioning expression recommendation tables).

Detailed information about related tools and procedures is found in the following sources:

  • Chapter 7: “Query Capture Facility”
  • Chapter 8: “Target Level Emulation”
  • “BEGIN QUERY CAPTURE” IN SQL Data Definition Language Syntax and Examples
  • “COLLECT DEMOGRAPHICS” in SQL Data Manipulation Language
  • “COLLECT STATISTICS (QCD Form)” in SQL Data Manipulation Language
  • “INITIATE INDEX ANALYSIS” in SQL Data Manipulation Language
  • “INITIATE PARTITION ANALYSIS” in SQL Data Manipulation Language
  • “DUMP EXPLAIN” in SQL Data Manipulation Language
  • “INSERT EXPLAIN” in SQL Data Manipulation Language
  • “RESTART INDEX ANALYSIS” in SQL Data Manipulation Language
  • Teradata Index Wizard User Guide
  • Teradata Viewpoint User Guide, chapter on the Stats Manager
  • Teradata Visual Explain User Guide
  • Teradata System Emulation Tool User Guide