15.00 - Using Join Indexes - Teradata Database

Teradata Database Design

Teradata Database
Release Number
Content Type
User Guide
Publication ID
English (United States)

Using Join Indexes

You can create a join index to perform any of the following operations:

  • Join multiple tables, optionally with aggregation, in a prejoin table.
  • Replicate all or a vertical subset of a single base table and distribute its rows by a primary index on a foreign key column to facilitate joins of very large tables by hashing them to the same AMP.
  • Aggregate one or more columns of a single table or the join results of multiple tables in a summary table.
  • Support querying only those rows that satisfy the conditions specified by its WHERE clause. This is known as a sparse join index.
  • If the index has a unique primary index, and a request specifies an equality condition on the columns that define the primary index for the index, then the index can be used for the access path in two-AMP join plans similarly to how USIs are used.
  • The guidelines for creating a join index are the same as those for defining any regular join query that is frequently executed or whose performance is critical. The only difference is that for a join index the join result is stored as a subtable and automatically maintained by Teradata Database. For the syntax of