Rules and Restrictions for Join Indexes - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™

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