Function of IndexRecommendations
Contains information about the index recommendations generated by INITIATE INDEX ANALYSIS.
IndexRecommendations 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 |
|
| 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 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 Teradata Vantage™ - SQL Data Manipulation Language, B035-1146). |
| 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.
|
| IndexTypeText | The textual representation of IndexType. The valid IndexTypeText strings and their meanings are as follows:
|
| 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.
|
| IndexDDL | The DDL text of the CREATE INDEX, DROP INDEX, CREATE JOIN INDEX, or DROP JOIN INDEX request for the index. |
| 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 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 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.
|
| TimeLimit | A user-specified time limit, in whole number of minutes, for the duration of the analysis. |