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