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:
- 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. Because of this requirement, any primary index access returns a single row, and only a single row partition must be probed to access that row.
- If you cannot include the partitioning columns in the primary index definition for a row-partitioned table, primary index access will also perform 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.
- You can achieve good performance by defining a NUSI on the NUPI column set for the partitioned table.
- If for some reason you cannot specify a partitioning key as a condition in a query against a row-partitioned table, you should consider the following alternative methods, where appropriate:
IF you cannot provide the value for the partitioning key in the WHERE clause, the PPI is a NUSI, and its values are … THEN consider creating this type of index on the primary index columns of the 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.
- 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.