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

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K
column_name
One of a list of multiple columns to be included for row compression in the join index. This column set represents the repeated portion of the join index row. Together with the first set of parenthetical fixed columns, these columns form the compressed portion of a compressed join index definition.
For more information about row compression for join indexes, see “CREATE JOIN INDEX” in SQL Data Definition Language - Detailed Topics, B035-1184.
You cannot define a partitioned primary index for a join index that is also compressed.

A column-partitioned join index cannot be row-compressed.
Columns in a join index cannot have any of the following data types: BLOB, CLOB, JSON, LOB UDT, VARIANT-TYPE, ARRAY/VARRAY, or Geospatial.
If two specified columns have the same name, both names must be aliased using a column name alias. See SQL Fundamentals, B035-1141.
You cannot specify the system-derived columns PARTITION or PARTITION#Ln, where n is an integer from 1 through 62, as part of the column_name list. 
You can, however, 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 a base table column. 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. For details, see SQL Request and Transaction Processing, B035-1142.

You can base an expression on a UDT column or an expression that references at least one column with the exception of the following: expressions that involve aggregate or OLAP functions, UDF expressions, and built-in functions that are explicitly prohibited 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. You can alias the column_name with a column name alias.
ROWID
You can specify the keyword ROWID as one of the values for column_name to enable the Optimizer to join a partial covering index to its base table to access any non-covered columns.
You cannot specify ROWID in both the fixed and repeating column_name group columns. If you reference a 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.
To reference multiple tables in the join index definition, you must fully qualify each ROWID specification. If you reference a ROWID column name alias in the select list of a join index definition, then you can also reference that column name alias in a CREATE INDEX request that creates a secondary index on the join index. You can only specify ROWID in the outermost select list of a CREATE JOIN INDEX statement. Do not specify row compression for aggregate join indexes.
You can specify up to 64 columns for the repeating column set. If you specify a repeating column set, you cannot specify more than 64 columns for the set because the total column limit for a compressed join index is 128 columns. Any number of repeating columns can be stored for a repeating column component of a join index. However, there is a physical limit in that the total size of any one fixed column set-repeated column set pair cannot exceed the Teradata Database row size limit. You can alias the ROWID with a column name alias.