Row-partitioned join indexes and value-ordered NUSIs are designed to optimize the performance of range queries. These guidelines apply to choosing between a value-ordered NUSI and a single-level partitioned join index only. Value-ordered NUSIs are not an alternative to multilevel partitioned join indexes.
Because you cannot define both a partitioning expression and a value-ordered NUSI on the same column set for a join index, nor can you define partitioning for a row-compressed join index, you must determine which is more likely to enhance the performance of a given query workload.
You should consider the following guidelines when determining whether to design a join index for a particular workload using a row-partitioned join index or using a nonpartitioned join index and adding a value-ordered NUSI to create a value-ordered access path to the rows:
- In general, if a partitioned join index suits your purposes, you should use a partitioned join index rather than using a value-ordered NUSI and a nonpartitioned join index.
- If row compression is defined on the join index, then you cannot define partitioning for it.
For this scenario, a value-ordered NUSI is your only join index design choice possibility for optimizing range queries.
- Each time an underlying base table for a join index is updated, the join index, too, must be updated.
If a value-ordered NUSI is defined on a join index, then it, too, must be updated each time the base table and join index rows are updated.
This additional maintenance is unnecessary when you define the join index with a row-partitioned PI, and row partition elimination makes updating a row-partitioned join index even faster than the equivalent update operation with a nonpartitioned join index. In this context, update includes the delete and update operations performed by DELETE, MERGE, and UPDATE SQL requests, but not insert operations.
- A PPI provides direct access to join index rows, while a value-ordered NUSI does not.
Using a NUSI to access rows is always an indirect operation, accessing the NUSI subtable before using the join index to access a row set. However, if the NUSI covers the query, there is no need to access the rows in the join index subtable.
A row-partitioned PI also provides a means for attaining better join and aggregation strategies on the primary index of the join index.
- If the primary index is specified as a query condition, a row-partitioned PI offers the additional benefit of enhanced direct join index row access using row partition elimination rather than the all-AMPs access approach required by NUSIs.
However, the comparative row access time depends on the selectivity of a particular value-ordered NUSI, the join index row size, and whether a value-ordered NUSI covers a particular query or not.
- If a join index column has more than 65,535 unique values, and the query workload you are designing the index for probes for a specific value, then a value-ordered NUSI is likely to be more selective than a join index partitioned on that column.