Guidelines for Choosing Between a Partitioned Join Index and a Value‑Ordered NUSI For Covering Range Queries With a Join Index
Partitioned join indexes and value‑ordered NUSIs are both designed to optimize the performance of range queries. Note that 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 partitioned join index or using an unpartitioned join index and adding a value‑ordered NUSI to create a value‑ordered access path to the rows:
For this scenario, a value‑ordered NUSI is your only join index design choice possibility for optimizing range queries.
If there is a value‑ordered NUSI 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 avoided entirely when you define the join index with a PPI, and row partition elimination makes updating a partitioned join index even faster than the equivalent update operation against an unpartitioned join index. The word update is used here in its generic sense to include the delete, and update (but not insert) operations performed by DELETE, MERGE, and UPDATE SQL requests.
Using a NUSI to access rows is always an indirect operation, accessing the NUSI subtable before being able to go back to 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.
Beside offering a direct path for row access, a PPI also provides a means for attaining better join and aggregation strategies on the primary index of the join index.
Ultimately, 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 given query or not.