17.10 - Compression of Hash Index Rows - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
Publication ID
B035-1184-171K
Language
English (United States)
The Teradata system uses a variety of compression methods, including:
  • Logical row compression
  • Compression of column values

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. For additional information, see Teradata Vantage™ - Database Design, B035-1094.

This topic is in regard to logical row compression.

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 consists of the explicitly-defined columns for the column_1 list. The repeating part is composed of the remaining columns, whether defined implicitly or explicitly. See Teradata Vantage™ - Database Design, B035-1094 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.