15.10 - Viewing Index and Partitioning Expression Recommendations - 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

INITIATE INDEX ANALYSIS requests insert their final recommendations into the specified IndexRecommendations QCD table (see “IndexRecommendations” on page 770), which you can then query. Teradata Database stores the definition of a recommended index in a CHARACTER column in the form of SQL text for CREATE INDEX and DROP INDEX requests.

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

INITIATE PARTITION ANALYSIS requests insert their recommendations into the specified PartitionRecommendations QCD table (see “PartitionRecommendations” on page 779). Teradata Database stores the definition of a recommended partitioning expression in a CHARACTER column in the form of SQL text for a PARTITION BY clause for an ALTER TABLE … MODIFY PRIMARY INDEX request.

A separate row is stored in each of these tables for each workload query that is impacted by the recommendation.

See “INITIATE INDEX ANALYSIS” and “INITIATE PARTITION ANALYSIS” in SQL Data Manipulation Language for the syntax and other information about these statements.

In the case of Secondary and Join Indexes, there is a row for every query whose new cost benefits from the recommended index.

The individual columns of a recommended composite secondary index are stored separately in QCD table IndexColumns (see “IndexColumns” on page 767).

In the case of partitioning expression recommendations, there is a row for every query whose cost either benefits or suffers from the recommended partitioning expression. The fact that the partitioning expression is recommended means that it reduces the total workload cost, but it is possible that the cost of one or more individual queries within the specified workload has increased.

Such rows can be identified in the QCD table PartitionRecommendations (see “PartitionRecommendations” on page 779) by comparing the estimated new cost with the original cost, both of which are stored in separate columns (named OriginalCost and NewCost, respectively) within the relevant row.

For obvious reasons, you should pay particular attention to any such rows prior to implementing a recommended partitioning expression.

To provide additional flexibility in displaying the recommendations, the system stores the individual details for a given recommendation in separate columns and rows in another set of QCD tables.

For example, the individual columns of a recommended composite secondary index are stored separately in QCD table IndexColumns (see “IndexColumns” on page 767).

Similarly, the individual range-start, range-end, and range-size values that make up a recommended RANGE_N partitioning expression are stored in QCD table RangePartExpr (see “RangePartExpr” on page 799).

For recommended partitioning expressions, an INITIATE PARTITION ANALYSIS request stores slightly different information in PartitionRecommendations:

  • Teradata Database does not record the estimated time required to add a recommended partitioning expression.
  • Teradata Database does record the disk space required to store the internal partition number in the base table or index subtable rows.
  • In addition to SQL text definitions and query cost information, the final index recommendations from an INITIATE INDEX ANALYSIS request also include the following information in IndexRecommendations:

  • The estimated time required to create a recommended secondary or join index.
  • The disk space required to store a recommended secondary or join index subtable.
  • The additional maintenance costs incurred by SQL update requests that modify columns in the recommended indexes.
  • Note: INITIATE INDEX ANALYSIS is not valid for geospatial NUSIs.