Tactical Query and Row-Partitioned Table Recommendations | VantageCloud Lake - Recommendations for Tactical Queries and Row-Partitioned Tables - Teradata VantageCloud Lake

When executing tactical queries based on primary index access to row-partitioned tables, consider the following suggestions for enhancing query performance, listed in order of greatest general benefit:

1. The best performance is achieved when both of the following conditions are true:
   • The row-partitioned table is defined with a unique primary index.
   • The primary index definition also contains the partitioning columns for the table.

   All UPIs on partitioned tables must contain the partitioning columns. Therefore, any primary index access returns a single row, and only a single row partition must be probed to access that row.

2. If you cannot include the partitioning columns in the primary index definition for a row-partitioned table, primary index access also performs well if you specify a value for the partitioning key WHERE clause as an additional constraint. This specification eliminates the need to probe all of the partitions.
3. You can achieve good performance by defining a NUSI on the NUPI column set for the partitioned table.
4. If you cannot specify a partitioning key in the WHERE clause in a query against a row-partitioned table, and the PPI is a NUSI:

   NUSI Values	Index to Consider Creating on Primary Index Columns ossf Row-PPI
   Unique	USI
   Not unique	Global join index

   Either design strategy avoids the necessity of probing each partition because the indexes point directly to each relevant physical row.

5. If none of these methods is appropriate for your workloads, consider defining the row-partitioned table with fewer partitions. By making the row-partition granularity more coarse, you reduce the level of probing required for a primary index access.