JoinIndexColumns - Analytics Database - Teradata Vantage

SQL Request and Transaction Processing
Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-10-04
dita:mapPath
zfm1628111633230.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
evd1472255317510
lifecycle
latest
Product Category
Teradata Vantage™

Function of JoinIndexColumns

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

JoinIndexColumns Table Definition

The following CREATE TABLE request defines JoinIndexColumns:

   CREATE TABLE JoinIndexColumns (
     WorkLoadID           INTEGER NOT NULL,
     RecommendationID     INTEGER NOT NULL,
     TableID              BYTE(6) NOT NULL,
     JINumber             INTEGER NOT NULL,
     ColumnName           VARCHAR128 CHARACTER SET UNICODE
                          NOT CASESPECIFIC DEFAULT NULL,
     AliasName            VARCHAR128 CHARACTER SET UNICODE
                          NOT CASESPECIFIC DEFAULT NULL,
     Field1Flag           CHARACTER(1) CHARACTER SET LATIN
                          NOT CASESPECIFIC DEFAULT NULL,
     Field2Flag           CHARACTER(1) CHARACTER SET LATIN
                          NOT CASESPECIFIC DEFAULT NULL,
     RowIDFlag            CHARACTER(1) CHARACTER SET LATIN
                          NOT CASESPECIFIC DEFAULT NULL,
     AggregateFunc        BYTEINT DEFAULT NULL,
     PrimaryIndexPosition BYTEINT DEFAULT NULL,
     GroupByPosition      BYTEINT DEFAULT NULL)
   PRIMARY INDEX (RecommendationID, TableID, JINumber);

Attribute Definitions for JoinIndexColumns

The following table defines the JoinIndexColumns table attributes:

Attribute Description
WorkLoadID Uniquely identifies the workload analyzed to create this join index recommendation.
RecommendationID
  • Uniquely identifies a set of index recommendations in the IndexRecommendations table.
  • Partial NUPI for the table.
TableID
  • The unique internal identifier in the IndexRecommendations table for the base table on which the join index is defined.
  • Partial NUPI for the table.
JINumber
  • The system-assigned sequence number for the join index in the IndexRecommendations table.
  • Partial NUPI for the table.
ColumnName Name of the join index column.
AliasName The correlation name assigned to a column or aggregate function in the join index definition.
Field1Flag Indicates whether the column is part of the column_1 (uncompressed columns) select list in the join index definition.
  • If F, the column is not part of the column_1 select list.
  • If T, the column is part of the column_1 select list.
Field2Flag Indicates whether the column is part of the column_2 (compressed columns) select list in the join index definition.
  • If F, the column is not part of the column_2 select list.
  • If T, the column is part of the column_2 select list.
RowIDFlag Indicates whether the value for the column is the reserved word ROWID.
  • If F, the value for the column is not ROWID.
  • If T, the value for the column is ROWID.
AggregateFunc Indicates whether an aggregate function is applied to ColumnName and, if so, the type of aggregation performed.
  • If 0, there is no aggregation.
  • If 1, the column is used in an aggregate SUM operation.
  • If 2, the column is used in an aggregate COUNT operation.
  • If 3, the column is used in a COUNT(*) operation.
  • If 4, the column is used in an aggregate MIN operation.
  • If 5, the column is used in an aggregate MAX operation.
PrimaryIndexPosition Indicates whether ColumnName is a component of the primary index for the join index.
  • If 0, the column is not part of the primary index definition for the join index.
  • If >1, the column is part of the primary index definition for the join index.

    The value represents the position of ColumnName within the primary index definition for the join index.
GroupByPosition Indicates whether ColumnName is a component of the GROUP BY clause in the join index definition.
  • If 0, the column is not part of the GROUP BY specification for the join index.
  • If >1, the column is part of the GROUP BY specification for the join index.

    The value represents the position of ColumnName within the GROUP BY specification for the join index.