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

Captures the columns that form the join index identified by JINumber during index analysis by the Teradata Index Wizard.

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

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.