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

column_name

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 first 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 Teradata Vantage™ - SQL Request and Transaction Processing 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. For more information, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.

All such expressions must be aliased. Otherwise, Vantage 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 Vantage row size limit.

ROWID

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.