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