15.10 - Index and Partitioning Expression Validation - Teradata Database

Teradata Database SQL Request and Transaction Processing

Teradata Database
Programming Reference
User Guide

You can validate the indexes and partitioning expressions recommended by the Index Wizard by comparing query response latencies with the same workload against the existing index or partitioning expression set against response latencies for the same query set and workload using the recommended index and partitioning expression sets. This is an optional, but recommended, step in the database query analysis process.

It is expected that you will often validate the union of INITIATE INDEX ANALYSIS and INITIATE PARTITION ANALYSIS recommendations for a given workload, or perhaps more likely, a selected subset of those recommendations, in an integrated validation session.

Note: INITIATE INDEX ANALYSIS is not valid for geospatial NUSIs.

You can also validate your own provisional indexes and partitioning expressions using the validation mode capabilities of Teradata Database (see “DIAGNOSTIC ‘Validate Index’” in SQL Data Manipulation Language for details about how to initiate and use a validation mode session).

The index and partitioning expression recommendations from an Analysis operation can be further evaluated in a special test mode referred to as Validation mode. Validation mode is essentially a special session mode that permits query capture with simulated indexes or row partitioning.

You can initiate a validation mode session by submitting a DIAGNOSTIC “Validate Index” request (see SQL Data Manipulation Language for the syntax and usage notes for this statement).

The captured plan that you validate includes any cost benefits or degradations realized by having the simulated index or partitioning expression defined and emulated.

While a session is in Validation mode, you can collect additional statistics that were not available during Analysis in order to provide more accurate costing. Unlike Analysis, there is no reason not to perform the Validation phase on a production system.

Teradata Database emulates indexes by issuing appropriate CREATE INDEX and CREATE JOIN INDEX statements that record the definition in a private session context. The index definitions are not stored in the dictionary, nor are the index subtables actually populated.

Similarly, Teradata Database emulates recommended partitioning expressions by issuing appropriate ALTER TABLE … MODIFY requests. Note that ALTER TABLE requests of this form are not ordinarily allowed on populated tables. However, they are permitted in Validation mode as a method for specifying an emulated partitioned table.

In addition to evaluating recommendations from a prior Analysis, Validation mode can also be used to evaluate user‑specified indexes or partitioning expressions. This capability is commonly referred to as What If? analysis, and it allows sophisticated users to experiment with their own candidate indexes and partitioning expressions. The only restriction is that any user‑specified candidates tested while your session is in Validation mode must be of the same type and class recommended by Analysis. For example, you cannot validate a multitable join index in validation mode because it is not supported in Analysis.

A given Validation mode session can include multiple simulated indexes or partitioning expressions or both. Unlike Analysis, it is possible to evaluate candidate PPIs alongside other candidate index types in the same Validation operation.

See SQL Data Manipulation Language for additional information about validating indexes recommended by the Teradata Index Wizard.

See Teradata Index Wizard User Guide for details about this process.

The process of validating recommended indexes and partitioning expressions is indicated by the following graphic. For any callout of Index in the graphic, you should read Index or Partitioning Expression, or both.