IndexMaintenance - Advanced SQL Engine - Teradata Database

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: