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 635 for definitions of the two types of compression) can be compressed. Join index rows may be hashed on their primary index or primary AMP index, or have no primary index. Note that a join index with a primary AMP index or no primary index must also be column partitioned. Join indexed tables can have nonunique secondary indexes, and their indexes are stored just like primary, primary AMP, and nonunique 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 row-compressed join index are stored. Repeated field value storage is too complex to describe in this summary. See Chapter 10: “Join and Hash Indexes” under “Join Index Storage” on page 549 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 451).
See “Compression Types Supported by Teradata Database” on page 635 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 10: “Join and Hash Indexes”.