Row-partitioned primary indexes and value-ordered NUSIs are both designed to optimize the performance of range queries. Because you cannot define both a PPI and a value-ordered primary index on the same join index (nor can you define a PPI for a row-compressed join index), you must determine which is more likely to enhance the performance of a given query workload for those situations that exclude using a join index with a value-ordered primary index.
A value-ordered primary index that meets the restriction of a 4-byte maximum column size is the preferred choice.This cannot be used for an MLPPI because MLPPIs do not support value-ordered NUSIs by definition.
Consider creating a multilevel partitioning on the base table as an alternative if you expect the usage to be roughly equivalent to a value-ordered NUSI on a join index plus a non-value-ordered NUSI.
A join index with a value-ordered primary index is the preferred choice over a value-ordered NUSI.
- Row partitioning on the join index is better than a value-ordered NUSI and an nonpartitioned primary index on the join index.
- If row compression is defined on the join index, then you cannot define partitioning for it.
For this scenario, a value-ordered primary index or value-ordered NUSI are your only join index design choice possibilities for optimizing range queries.
- Each time an underlying base table for a join index is updated, the join index also must be updated.
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.
You can avoid this additional maintenance when you define the join index with row partitioning. Row partition elimination makes updating a join index row even faster than the equivalent update operation against an nonpartitioned primary index join index. "Update" is used in a generic sense to include the delete and update operations performed by the DELETE, MERGE, and UPDATE SQL statements, but excluding insert operations performed by the SQL INSERT and MERGE statements, where row partition elimination is not a factor.
Row partitioning can improve insert operations if the inserted rows are clustered in the data blocks corresponding to the partitions. The number of data blocks read and written is reduced compared to the nonpartitioned primary index join index case where the inserted rows are scattered among all data blocks.
Because of the way the rows of a column-partitioned join index are distributed to the AMPs, do not expect to see the positive effects of data block clustering for singleton INSERT requests. Expect to see positive effects for large INSERT ... SELECT loads into column-partitioned join indexes when the base table is loaded with rows using an INSERT ... SELECT request.
- Row-partitioned join indexes offer the benefit of providing direct access to join index rows, while a value-ordered NUSI does not.
Using a NUSI to access rows is an indirect operation, touching the NUSI subtable before being able to go back to the join index to access a row set.
Besides offering a direct path for row access, row partitioning provides a means for attaining better join and aggregation strategies on the primary index of the join index.
- If you specify the primary index column set in the query, row partitioning and join indexes offer the additional benefit of enhanced direct join index row access using row partition elimination.
The comparative row access times ultimately depend on the selectivity of the value-ordered NUSI, the join index row size, and whether a value-ordered NUSI covers a given query or not.
- If a join index partitioning column has more than 65,535 unique values, and the query workload you are designing the index for probes for a specific value, a value-ordered NUSI is likely to be more selective than a join index partitioned on that column.
Because NUSI access is indirect, the system may read data blocks to retrieve a small number of rows using a NUSI, while row-partitioned index access is direct, with like rows being clustered together, so row-partitioned join index read operations are typically more efficient than value-ordered NUSI read operations.