15.00 - Join Index Storage - Teradata Database

Teradata Database Design

Teradata Database
User Guide

Join Index Storage

For the most part, join index storage is identical to standard base table storage except that the rows of join indexes (as distinguished from column value compression. See “Compression Types Supported by Teradata Database” on page 695 for definitions of the two types of compression) can be compressed. Join index rows are hashed on their primary index. Join index tables can be indexed, and their indexes are stored just like primary and secondary indexes for any other base table.

The major difference in storage between join indexes and base tables is the manner in which the repeated field values of a join index are stored. Repeated field value storage is too complex to describe in this summary. See Chapter 11: “Join and Hash Indexes” under “Join Index Storage” on page 598 for details about how repeated field values are stored.

When possible, join indexes also inherit the multivalue compression characteristics of their underlying base tables (see “Default Column Multivalue Compression for Join Index Columns When the Referenced Base Table Column Is Compressed” on page 501).

See “Compression Types Supported by Teradata Database” on page 695 for a comparison of row and multi‑value compression.

A join index can be stored either in hash-order or in value-order, depending on its intended use. Value-ordered join indexes are restricted to certain data types and field lengths. See “CREATE JOIN INDEX” in SQL Data Definition Language for details.

Related Topic

For additional usage information and design tips about join indexes, see Chapter 11: “Join and Hash Indexes”.