Default Column Multivalue Compression for Join Index Columns When the Referenced Base
Table Column Is Compressed
When you create a join index, Teradata Database automatically transfers any column
multivalue compression defined on the base table, with a few exceptions, to the join
index definition. In contrast, hash indexes do not inherit the multivalue compression defined for the columns of their parent base table.
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 multivalue 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 compression transfers to a multivalue 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 multivalue 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 multivalue compression does not transfer to a join index definition for any of the columns that are components of
a partitioned primary index, a partitioning expression for a PPI join index, or a
partitioning expression for a column‑partitioned join index.
Base table column multivalue compression does not transfer to a join index column if the column is specified as the argument for any
of the following functions.
COUNT
EXTRACT
MIN
MAX
SUM
Base table column multivalue compression does not transfer to a column in a compressed join index that has indexes defined on its column_1 and column_2 sets.
Base table column multivalue 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.