17.10 - IndexColumns - 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 IndexColumns

Captures the columns that form the index identified by IndexID during index analysis using INITIATE INDEX ANALYSIS.

IndexColumns Table Definition

The following CREATE TABLE request defines the IndexColumns table:

   CREATE TABLE IndexColumns(
     WorkLoadID       INTEGER NOT NULL,
     RecommendationID INTEGER NOT NULL,
     TableID          BYTE(6) NOT NULL,
     IndexID          INTEGER NOT NULL,
     ColumnName       VARCHAR128 CHARACTER SET UNICODE
                      NOT CASESPECIFIC NOT NULL)
   PRIMARY INDEX ( RecommendationID, TableID, IndexID );

Attribute Definitions for IndexColumns

The following table defines the IndexColumns table attributes:

Attribute Definition
WorkloadID Uniquely identifies the workload analyzed to create this secondary index recommendation.
RecommendationID
  • Uniquely identifies the recommendation ID in the IndexRecommendations table.
  • Partial NUPI for the table.
TableID
  • Uniquely identifies the table ID in the IndexRecommendations table.
  • Partial NUPI for the table.
IndexID
  • Uniquely identifies the index in the IndexRecommendations table.
  • Partial NUPI for the table.
ColumnName The name of the column in the index.

The number of rows in IndexColumns for a given index is equal to the number of columns in the index, so any composite index has multiple rows associated with it.