A join index offers the same benefits as a standard secondary index in that it, like the standard secondary index, has the following properties.
- Optional
- User defined
- Maintained by the system
- Transparent to end users
- Immediately available to the Optimizer
- If a covering index, considered by the Optimizer for a merge join
However, a join index offers the following performance benefits over a NUSI.
| Join Index | Why Performance Improves |
|---|---|
| Defined using joins on one or more columns from two or more base tables | Join index eliminates need to perform join step every time joining query is processed. |
| Used for direct access in place of one or more base tables, if the Optimizer determines that the join index covers most or all of the query. | Join index eliminates I/Os and resource usage required to access base tables. |
| Limited to only certain data types of your choice, such as Date | Join index allows direct access to join index rows within specified value-order range. |
| Single-table join index with a FK primary index | Join index reduces I/Os and message traffic because row redistribution is optional. Row redistribution is optional because the following are hashed to the same AMP:
|
| Defined with an outer join | Join index does the following:
|
| Created using aggregates | Join index eliminates aggregate calculations and join step for every query requiring the join and aggregate. |
For more information on the syntax and usage of join indexes, see CREATE JOIN INDEX.