15.00 - Recommendations for Tactical Queries and Partitioned Tables - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Recommendations for Tactical Queries and Partitioned Tables

When executing tactical queries based on primary index access to partitioned tables, consider the followings 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 partitioned table is defined with a unique primary index.
  • The partitioned definition also contains the partitioning key for the table.
  • All UPIs on partitioned tables must contain the partitioning key. Because of this requirement, any primary index access returns a single row, and only a single partition must be probed to access that row.

    2 If you cannot include the partitioning key in the primary index definition for a 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.

    3 You can achieve good performance by defining a NUSI on the NUPI column set for the partitioned table.

    4 If for some reason you cannot specify a partitioning key as a condition in a query against a 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 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 partitioned table with fewer partitions. By making the partition granularity more coarse, you reduce the level of probing required for a primary index access.