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 large tables by hashing the rows 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 run 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 Vantage.
- Performance and Join Indexes
- Partial Covering Multitable Join Indexes
- Covering Bind Terms
- Using Single-Table Join Indexes
- Using Outer Joins to Define Join Indexes
- Defining Join Indexes with Inequality Conditions
- Defining Join Indexes on Expressions
- Refreshing Join Indexes
- Using Aggregate Join Indexes
- Join Indexes and the Optimizer
- System Processing of Join Indexes
- Join Index Optimizations
- Protecting a Join Index with Fallback
- Collecting Statistics for Join Indexes
- Costing Considerations for Join Indexes
- Join Indexes and NUSIs