15.00 - Guidelines for Choosing Between a Partitioned Join Index and a Value-Ordered NUSI For Covering Range Queries With a Join Index - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Teradata Database
Release Number
Content Type
Programming Reference
Publication ID
English (United States)

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:

  • The most general guideline is that if you can achieve what you need to achieve using a partitioned join index, then you should do that rather than using a value‑ordered NUSI and an unpartitioned 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 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.

  • All things being equal, a PPI offers 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, 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.

  • If the primary index is specified as a query condition, a PPI 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.
  • 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.

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