15.00 - Compression of Join Index Column Values - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

Compression of Join Index Column Values

When you create a join index, Teradata Database automatically transfers any column multi-value compression defined on its base table set, with a few exceptions, to the join index definition. This is different from the situation for hash indexes, which cannot inherit the multi-value compression characteristics of their parent base tables.

The following rules and restrictions apply to automatically transferring the column compression characteristics of a base table to its underlying join index columns. All of these rules and restrictions must be met for base table column multi-value compression to transfer to the join index definition.

  • Base table column multi-value compression transfers to a join index definition even if there is an alias name specified for the columns in the join index definition.
  • Base table column multi-value compression transfers to a multitable join index definition only up to the point that the maximum table header length of the join index is exceeded.
  • The CREATE JOIN INDEX request does not abort at that point, but the transfer of column multi-value compression from the base table to the join index definition stops.

  • Base table column multi-value compression does not transfer to a join index definition if the column is a component of the primary index for the join index.
  • Base table column multi-value compression does not transfer to a join index definition for any of the columns that are components of a partitioned primary index or the partitioning expression for the join index.
  • Base table column multi-value compression does not transfer to a join index column if the column is specified as the argument for aggregate or EXTRACT functions.
  • Base table column multi-value compression does not transfer to a column in a compressed join index if the column is a component of a NUSI defined on the column_1 and column_2 indexes.
  • If you use an ALTER TABLE request to modify the compression characteristics of any base table columns that are also components of a join index, Teradata Database returns a warning message advising you that you must recreate the join index to reflect those compression changes. See “ALTER TABLE (Basic Table Parameters)” on page 31.
  • Base table column multi-value compression does not transfer to a join index definition if the column is a component of an ORDER BY clause in the join index definition.