17.10 - Rules and Restrictions for Join Indexes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

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

You can create a join index with column partitioning and a primary AMP index, primary index, or no primary index, with some restrictions such as the join index must be noncompressed and based on a single table.

Join indexes can improve query response times in the following ways:

  • Multitable join indexes prejoin tables so that the join result is fetched from the join index directly to answer applicable queries instead of calculating the joins dynamically.
  • Single-table join indexes effectively redistribute the rows in their underlying base table by choosing a different partitioning or primary index than the base table to make the joins between the join index and other tables or indexes more efficient.

    You can also use a single-table join index to establish an alternate access opportunity.

  • Aggregate join indexes preaggregate results so they can be used to answer aggregation queries directly.
  • Sparse join indexes store a subset of the rows from their underlying base table set based on a WHERE clause condition. This makes it possible for a smaller join index to be used in answering queries when applicable.

A join index can be a combination of the individual types, such as a join index that has all of the following properties.

  • Sparse
  • Aggregate
  • Multitable