Join Indexes Versus NUSIs
A join index offers the same benefits as a standard secondary index in that it, like the standard secondary index, has the following properties.
However, a join index offers the following performance benefits over a NUSI.
|
IF a join index is… |
THEN performance improves by… |
|
defined using joins on one or more columns from two or more base tables |
eliminating the need to perform the join step every time a joining query is processed. |
|
used for direct access in place of some or all of its base tables, if the Optimizer determines that it covers most or all of the query. |
eliminating the I/Os and resource usage required to access the base tables. |
|
limited to only certain data types of your choice, such as Date |
allowing direct access to the join index rows within the specified value-order range. |
|
a single-table join index with a FK primary index |
reducing I/Os and message traffic because row redistribution is not required, since the following are hashed to the same AMP: |
|
defined with an outer join |
|
|
created using aggregates |
eliminating both the aggregate calculations and the join step for every query requiring the join and aggregate. |
For more information on the syntax, applications, restrictions, and benefits of join indexes, see SQL Data Definition Language.