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 ...
