15.10 - Fixed Column Set for Join Index Row Compression - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
December 2015
Programming Reference
One of a list of multiple columns to be included for row compression in the join index. This column set represents the fixed portion of the join index row. Together with the second set of parenthetical repeating columns, these columns form the compressed portion of a compressed join index definition.
You can alias column_name with a column name alias.
You cannot specify the system-derived columns PARTITION or PARTITION#Ln as part of the column_name list.
However, you can specify a user-defined column named PARTITION or PARTITION#L n.
A join index defined with an expression in its select list has more restricted coverage than a join index defined using only base table columns. However, if you define a complex expression in the definition of a single-table join index, always collect statistics on it because the Optimizer can use those statistics directly to estimate the selectivity of those expressions for base table expressions specified in a query predicate. See SQL Request and Transaction Processing, B035-1142 for details.

You can base an expression on a UDT column or on an expression that references at least one column with the exception of the following.
  • Expressions based on aggregate or OLAP functions
  • Expressions based on a UDF
  • Expressions based on explicitly prohibited built-in functions such as DEFAULT and PARTITION. See SQL Functions, Operators, Expressions, and Predicates, B035-1145 for details about these functions.
All such expressions must be aliased. Otherwise, Teradata Database returns an error to the requestor.
If 2 of the specified columns have the same name, you must alias both names using a column name alias.
You can specify up to 64 columns for the fixed column set, because the total column limit for a compressed join index is 128 columns. Any number of fixed columns can be stored for a fixed column component of a join index. However, there is a physical limit in that the total size of any one fixed portion-repeated portion pair cannot exceed the Teradata Database row size limit.
Specify the keyword ROWID as one of the values for the column_list to enable the Optimizer to join a partial covering index to its base table to access the non-covered columns. You cannot specify ROWID in both the fixed and repeating column_name group columns.
You can only specify ROWID in the outermost select list of a CREATE JOIN INDEX request.
You can alias ROWID with a column name alias.
If you refer to multiple tables in the join index definition, you must fully qualify each ROWID specification.
If you specify ROWID in a GROUP BY clause, you must define a column name alias for it. The literal ROWID is not valid in the column list argument of a GROUP BY specification.
If you refer to a ROWID column name alias in the select list of your join index definition, you can also refer to that column name alias in a CREATE INDEX request that creates a secondary index on the join index. However, you cannot refer directly to the ROWID keyword in a CREATE INDEX request.