Designing for Range Queries: Guidelines for Choosing Between a PPI and a Value‑Ordered NUSI
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.
In general, a value‑ordered primary index is the preferred choice if it meets the restriction of a 4‑byte maximum column size. Note that this cannot be used for an MLPPI because MLPPIs do not support value‑ordered NUSIs by definition.
You might want to consider creating a multilevel partitioning on the base table as an alternative if the usage would be roughly equivalent to a value‑ordered NUSI on a join index plus a non-value‑ordered NUSI.
In nearly all cases, a join index with a value‑ordered primary index is the preferred choice over a value‑ordered NUSI.
You should consider the following guidelines when determining whether to design a join index for a particular workload using row partitioning on the join index or using an nonpartitioned primary index and adding a value‑ordered NUSI to create a value‑ordered access path to the rows:
For this scenario, a value‑ordered primary index or value‑ordered NUSI are your only join index design choice possibilities 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.
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. The word 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 also improve insert operations if the inserted rows are clustered in the data blocks corresponding to the partitions. In this case, the number of data blocks read and written is reduced compared with the nonpartitioned primary index join index case where the inserted rows are scattered among all the data blocks. Because of the way that the rows of a column‑partitioned join index are distributed to the AMPs (see “Row Allocation for Teradata Parallel Data Pump” on page 237), you should not expect to see the positive effects of data block clustering for singleton INSERT requests. However, you should see very 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.
Using a NUSI to access rows is always 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. This benefit does not extend to column‑partitioned join indexes because they do not have a primary index.
The comparative row access times ultimately depend 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.
Note that because NUSI access is indirect, the system might read entire data blocks to retrieve one or a few 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 usually far more efficient than value‑ordered NUSI read operations.