Creating a join index to improve performance

A join index is a data structure that contains data from one or more tables, with or without aggregation:

• The columns of two or more tables
• Two or more columns of a single table

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 persistently stored and automatically maintained.

Join indexes are used in Teradata to:

• Pre-join tables that are often joined in queries.
• Pre-aggregate summary data.
• Create materialized views of data. In Teradata, it is described as a link between view and index.
• Create a sparse data set.

A join index helps in increasing the efficiency and performance of the queries containing joins:

•  A join index is accessed repeatedly...