16.10 - IndexRecommendations - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
16.10
created_date
June 2017
category
Programming Reference
User Guide
featnum
B035-1142-161K

Function of IndexRecommendations

Contains information about the index recommendations made by the Teradata Index Wizard utility.

You can query this table to retrieve the index definitions the Index Wizard recommends. IndexRecommendations also records the options specified during index analysis for later retrieval.

IndexRecommendations Table Definition

The following CREATE TABLE request defines the IndexRecommendations table:

   CREATE TABLE IndexRecommendations(
     WorkLoadID          INTEGER NOT NULL,
     UserName            VARCHAR128 CHARACTER SET UNICODE
                         NOT CASESPECIFIC NOT NULL,
     TimeOfAnalysis      TIMESTAMP(0) NOT NULL,
     RecommendationID    INTEGER NOT NULL,
     QueryID             INTEGER NOT NULL,
     IndexID             INTEGER,
     IndexNameTag        VARCHAR128 CHARACTER SET UNICODE
                         NOT CASESPECIFIC NOT NULL,
     TableName           VARCHAR128 CHARACTER SET UNICODE
                         NOT CASESPECIFIC NOT NULL,
     DatabaseName        VARCHAR128 CHARACTER SET UNICODE
                         NOT CASESPECIFIC NOT NULL,
     TableID             BYTE(6) NOT NULL,
     IndexType           INTEGER,
     IndexTypeText       VARCHAR(30) CHARACTER SET LATIN
                         NOT CASESPECIFIC,
     StatisticsInfo      VARBYTE(16383),
     OriginalCost        FLOAT,
     NewCost             FLOAT,
     SpaceEstimate       FLOAT,
     TimeEstimate        FLOAT,
     DropFlag            CHARACTER(1)CHARACTER SET LATIN
                         NOT CASESPECIFIC,
     IndexDDL            VARCHAR(10000) CHARACTER SET UNICODE
                         NOT CASESPECIFIC,
     StatsDDL            VARCHAR(10000) CHARACTER SET UNICODE
                         NOT CASESPECIFIC,
     Remarks             VARCHAR(1024) CHARACTER SET UNICODE
                         NOT CASESPECIFIC,
     AnalysisData        VARCHAR(2048) CHARACTER SET LATIN
                         NOT CASESPECIFIC,
     IndexesPerTable     SMALLINT DEFAULT NULL,
     SearchSpaceSize     SMALLINT,
     ChangeRateThreshold BYTEINT,
     ColumnPerIndex      SMALLINT,
     ColumnsPerJoinIndex SMALLINT DEFAULT NULL,
     IndexMaintMode      BYTEINT DEFAULT NULL,
     JINumber            INTEGER DEFAULT NULL,
     JITableName         VARCHAR128 CHARACTER SET UNICODE
                         NOT CASESPECIFIC DEFAULT NULL,
     TimeLimitExceeded   CHAR(1) CHARACTER SET LATIN NOT CASESPECIFIC)
     TimeLimit           INTEGER
   PRIMARY INDEX (WorkloadID);

Attribute Definitions for IndexRecommendations

The following table defines the IndexRecommendations table attributes:

Attribute Definition
WorkloadID
  • Uniquely identifies the workload.
  • NUPI for the table.
UserName Name of the user performing the index analysis.
TimeOfAnalysis The timestamp when the index recommendations were analyzed.

You can compare this column with the modified timestamp for TableName to verify the correctness of the recommendation before applying it to the system.

RecommendationID Uniquely identifies a set of index recommendations.
QueryID Uniquely identifies the QueryID of the workload for which the current entry is an index recommendation.
IndexID Uniquely identifies a unique secondary index recommended by the Teradata Index Wizard for a table.

Set null, meaning not applicable, when the value of IndexType is 5 or 6, indicating a join index.

IndexNameTag Name of the index recommendation as specified in the INITIATE INDEX ANALYSIS statement (see SQL Data Manipulation Language).
TableName Name of the table for which the row defines an index recommendation.
DatabaseName Name of the database containing TableName.
TableID The unique internal identifier for TableName.
IndexType A number that identifies the type of index recommended.

Each unique index type is associated with its own IndexTypeText.

  • 1 indicates the IndexTypeText is a Unique Secondary Index (USI).
  • 2 indicates the IndexTypeText is a Value-Ordered Secondary Index (VOSI).
  • 3 indicates the IndexTypeText is a Hash-Ordered Secondary Index (HOSI).
  • 4 indicates the IndexTypeText is a Nonunique Secondary Index (NUSI).
  • 5 indicates the IndexTypeText is a Simple Join Index (JI).
  • 6 indicates the IndexTypeText is an Aggregate Join Index (JIAGG).
IndexTypeText The textual representation of IndexType.

The valid IndexTypeText strings and their meanings are as follows:

  • HOSI is a Hash-Ordered Secondary Index.
  • JI is a Simple Join Index.
  • JIAGG is an Aggregate Join Index.
  • NUSI is a Nonunique Secondary Index.
  • USI is a Unique Secondary Index.
  • VOSI is a Value-Ordered Secondary Index.
StatisticsInfo The statistics, if any, used to make the index recommendations.
OriginalCost The estimated cost of the query in milliseconds before implementing the recommended indexes.
NewCost The estimated cost of the query in milliseconds after implementing the recommended indexes.
SpaceEstimate The estimated space in bytes the recommended index occupies when created.
TimeEstimate The estimated time in milliseconds required to implement the index recommendation.
DropFlag Identifies whether the specified index is to be added or dropped.
  • N means the index is not recommended for dropping.
  • Y means the index is recommended for dropping.
IndexDDL The DDL text of the CREATE INDEX, DROP INDEX, CREATE JOIN INDEX, or DROP JOIN INDEX request for the index recommended by the Teradata Index Wizard.
StatsDDL The DDL text of the COLLECT STATISTICS (QCD form) requests used for the analysis of the index.
Remarks Provides details on the analysis involved in making the index recommendation.
AnalysisData Reserved for future use.
IndexesPerTable The limit on the number of indexes on a given table as specified by the INITIATE INDEX ANALYSIS statement used to start this analysis.
SearchSpaceSize The maximum number of candidate indexes that are searched on a given table as specified by the INITIATE INDEX ANALYSIS statement used to start this analysis.
ChangeRateThreshold The threshold value of the column volatility as specified by the INITIATE INDEX ANALYSIS statement used to start this analysis.

Any column with a change rating less than ChangeRateThreshold is available for selection as a candidate index during index analysis.

ColumnsPerIndex The maximum number of columns permissible in the index as specified by the INITIATE INDEX ANALYSIS statement used to start this analysis.
ColumnsPerJoinIndex The integer value used during analysis to control the maximum number of columns in a recommended Join Index as specified by the Teradata Index Wizard or by the INITIATE INDEX ANALYSIS SET boundary_option specification.
IndexMaintMode The integer value used during analysis to control how estimated index maintenance costs are used.

The value is specified by the Teradata Index Wizard or using the INITIATE INDEX ANALYSIS SET boundary_option specification.

JINumber An integer value sequence number that identifies the recommended Join Index table for a given index analysis.

The column is set to null for index types other than Join Index.

JITableName A system-assigned name for the Join Index.

If JINumber is null, then so is JITableName.

The naming convention for JITableName is as follows: JI_RecommendationID_BaseTableName_JINumber, where JI is a literal string and the values for RecommendationID, BaseTableName, and JINumber are those used in the eponymously named columns of the row, converted to character format where necessary.

TimeLimitExceeded Indicates whether or not the recommendation was generated by an INITIATE INDEX ANALYSIS request whose specified time limit expired.
  • If F, the partition analysis was not interrupted because the specified INITIATE INDEX ANALYSIS time limit was exceeded.

    This is the default.

  • If T, the partition analysis was interrupted because the specified INITIATE INDEX ANALYSIS time limit was exceeded.

    This means that the final recommendations might have been affected.

TimeLimit A user-specified time limit, in whole number of minutes, for the duration of the analysis.