15.10 - IndexRecommendations - 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

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.

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);

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.