17.10 - Join and Hash Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

This section describes join and hash indexes. These indexes permit you to undertake a wide variety of physical denormalizations of the database without affecting the normalization of the logical and physical models.

You should always define an equivalent single-table join index rather than a hash index so as not to depend on the default choices that are made for a hash index to be the correct choices. Also, hash indexes have some limitations such as multivalue compression is not carried over to a hash index from the base table.

Other topics include information on how to estimate the overhead of various join indexes, Optimizer criteria for selecting a join index, special storage options offered by join indexes, and numerous examples.

See Design Issues for Tactical Queries for a description of the special design considerations that must be evaluated for using hash and join indexes to support tactical queries.

For information about design issues related to join indexes defined on temporal tables and system-defined join indexes, see Teradata Vantage™ - ANSI Temporal Table Support, B035-1186 and Teradata Vantage™ - Temporal Table Support, B035-1182.