15.00 - Compression of Hash Index Rows - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Compression of Hash Index Rows

Compression refers to a logical row compression in which multiple sets of non-repeating column values are appended to a single set of repeating column values. This allows the system to store the repeating value set only once, while any non-repeating column values are stored as logical segmental extensions of the base repeating set.

When the following is true, the system automatically compresses the rows of a hash index:

  • The ORDER BY column set is specified in the column_1 list and none of the columns in the order key is unique.
  • The primary index columns are specified in the column_1 list.
  • As the following table indicates, the primary index columns for a hash index are always part of the column_1 list, whether specified explicitly or not.


    IF you create the index …

    THEN the primary index columns …

    without a BY clause

    are part of the column_1 list by default.

    with a BY clause

    must be part of the column_1 list because all columns specified in the BY clause column list must also be specified in the column_1 list.

    Rows having the same values for the order key are compressed into a single physical row having fixed and repeating parts. If the columns do not fit into a single physical row, they spill over to additional physical rows as is necessary.

    The fixed part of the row is made up of the explicitly‑defined columns that define the column_1 list. The repeating part is composed of the remaining, implicitly‑defined columns.

    The system only compresses row sets together if they are inserted by the same INSERT statement. This means that rows that are subsequently inserted are not appended as logical rows to existing compressed row sets, but rather are compressed into their own self‑contained row sets.