17.10 - IndexMaintenance - Advanced SQL Engine - Teradata Database

Teradata Vantageā„¢ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

Function of IndexMaintenance

IndexMaintenance stores the estimated costs incurred by the INSERT, UPDATE, MERGE, or DELETE requests in maintaining the recommended indexes stored in IndexRecommendations.

IndexMaintenance contains one row for each SQL request-index combination where maintenance costs are required for the index. You can query this table directly to retrieve information about the estimated cost of maintaining the indexes for a given workload.

IndexMaintenance Table Definition

The following CREATE TABLE statement defines the IndexMaintenance table:

   CREATE TABLE IndexMaintenance (
     WorkLoadID       INTEGER NOT NULL,
     RecommendationID INTEGER NOT NULL,
     IndexNameTag     VARCHAR128 CHARACTER SET UNICODE NOT CASESPECIFIC
                      NOT NULL,
     SQLStatementID   INTEGER NOT NULL,
     SecondaryIndexID INTEGER DEFAULT NULL,
     BaseTableID      BYTE(6) NOT NULL,
     BaseTableName    VARCHAR128 CHARACTER SET UNICODE NOT CASESPECIFIC
                      NOT NULL,
     DatabaseName     VARCHAR128 CHARACTER SET UNICODE NOT CASESPECIFIC
                      NOT NULL,
     IndexType        INTEGER NOT NULL,
     IndexTypeText    VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
     JINumber         INTEGER DEFAULT NULL,
     MaintCosts       FLOAT DEFAULT 0)
   PRIMARY INDEX (WorkloadID);

Attribute Definitions for IndexMaintenance

The following table defines the IndexMaintenance table attributes:

Attribute Description
WorkloadID
  • Uniquely identifies the workload analyzed to create the attribute data.
  • NUPI for the table.
RecommendationID Uniquely identifies the recommendation ID in the IndexRecommendations table.
IndexNameTag User-specified name for the index analysis.
BaseTableID The ID of the base table that is being updated.
BaseTableName The name for the base table referenced by BaseTableID.
JINumber The sequentially assigned number for the recommended join index in the IndexRecommendations table.

This column is applicable only if the value for the IndexType column corresponds to a valid join index type.

SQLStatementID The value for QueryID in the Query table, which is a unique identifier for the query generated by the system when the query plan is captured.
SecondaryIndexID The IndexID of the recommended secondary index in the IndexRecommendations table.

This column is applicable only if the value for the IndexType column corresponds to a valid secondary index type.

IndexType A number that identifies the type of index recommended.

Each unique index type is associated with its own IndexTypeText.

The valid IndexType codes and their corresponding IndexTypeText strings are as follows:

  • If 1, IndexTypeText is USI.
  • If 2, IndexTypeText is VOSI.
  • If 3, IndexTypeText is HOSI.
  • If 4, IndexTypeText is NUSI.
  • If 5, IndexTypeText is JI.
  • If 6, IndexTypeText is 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.
MaintCosts The estimated cost in milliseconds to update the recommended index structures.