15.00 - Hash and Join Index Row Structures - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Hash and Join Index Row Structures

The row structure for non‑row compressed hash and join indexes is the same as that for a standard data table (with the exception that you cannot compress individual column values for hash indexes (which means that the presence bit array of a hash index indicates only the nullability of a column, not its compressibility), while the row structure for row compressed hash and join indexes is more like that of a secondary index (see “Sizing a Unique Secondary Index Subtable” on page 862 and “Sizing a Non‑Unique Secondary Index Subtable” on page 864).

Other than that exception, the only difference is the specific columns included in the base table data columns of the row. For all hash indexes, Teradata Database automatically adds the primary index columns of the base table (if not explicitly specified in the hash index definition) and its row uniqueness value. The system does not automatically add this information to join indexes.

Hash indexes are typically row compressed by default (see “Compression of Hash Index Rows” on page 607 for details), while you must explicitly specify row compression in a CREATE JOIN INDEX request to compress rows in the join index it creates.

Note that you cannot row compress either of the following hash and join index types:

  • A hash index defined with a PPI.
  • You cannot define any hash index with a partitioned primary index.

  • A join index defined with a PPI or with column partitioning.