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

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 recommended by the Teradata Index Wizard for a given workload.

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

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.