17.05 - Repeating Column Set for Join Index Row Compression - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1144-175K
Language
English (United States)
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 second 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 Teradata Vantage™ - 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, LOB UDT, VARIANT-TYPE, ARRAY/VARRAY, Geospatial, JSON, or DATASET.
If two specified columns have the same name, both names must be aliased using a column name alias. See Teradata Vantage™ - 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 Teradata Vantage™ - 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 or keywords that are explicitly prohibited such as DEFAULT or PARTITION. For information about the DEFAULT built-in function, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145. For information about the PARTITION keyword, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146. All such expressions must be aliased. Otherwise Vantage 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 Vantage row size limit. You can alias the ROWID with a column name alias.