15.00 - Compression of Hash Index Rows - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)

Compression of Hash Index Rows

The term compression is used to mean two entirely different things for the Teradata system. Both forms are lossless, meaning that the original data can be reconstructed exactly from their compressed forms:

  • When describing compression of hash and join indexes, compression refers to a logical row compression in which multiple sets of fixed, or 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 fixed column values are stored as logical segmental extensions of the base repeating set.
  • When describing compression of column values, compression refers to the storage of those values one time only in the table header, not in the row itself, and pointing to them by means of an array of presence bits in the row header. The method is called Dictionary Indexing, and it can be viewed as a variation of Run‑Length Encoding (see Database Design for additional information).
  • This topic refers to the first definition of compression in the preceding list.

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

  • The ordering column set is the same as the primary index column set of the base table.
  • The primary index of the base table is not unique.
  • 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 columns, whether defined implicitly or explicitly. See Database Design for a more detailed description of hash index row compression.

    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.

    Note that while you can compress hash index rows, you cannot compress individual column values for a hash index. Furthermore, unlike join index column values, hash index column values cannot inherit the compression characteristics of their parent base table.