15.00 - Designing for Range Queries: Guidelines for Choosing Between a PPI and a Value-Ordered NUSI - Teradata Database

Teradata Database Design

Teradata Database
User Guide

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:

  • It is better to use row partitioning on the join index than to use 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. 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.

  • 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 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.

  • 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 a particular 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.
  • 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.