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

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.

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.