15.00 - Default Column Multivalue Compression for Join Index Columns When the Referenced Base Table Column Is Compressed - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

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.