15.00 - Primary Index Value Retrieval Access: Partitioning Guidelines for Selection - Teradata Database

Teradata Database Design

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

Primary Index Value Retrieval Access: Partitioning Guidelines for Selection

The same guideline applies to partitioned access. The principal reason to define a table or uncompressed join index with row partitioning is to facilitate row partition elimination. Row partition elimination is analogous to how the Optimizer uses column projection and row restriction in that it eliminates partitions that are not relevant at the earliest possible stage in query processing.

The degree of row partition elimination depends both on the partitioning expression specified for the index and on the conditions specified in the query. Increasing the number of populated row partitions can degrade the performance of primary index access, joins, and aggregations on the primary index, but it also permits finer row partition elimination; therefore, it is critical to understand the nature of the applications that are the predominant users of a row‑partitioned table or uncompressed join index.

It is not always necessary for all values of the partitioning columns to be specified in a query for row partition elimination to occur (see “Row‑Partitioned and Nonpartitioned Primary Index Access for Typical Operations” on page 403 for details about the relative performance implications of various access methods on tables with partitioned or nonpartitioned primary indexes).

A PPI provides optimal access to base table or join index rows while also providing efficient join and aggregation strategies on the primary index in other situations. With a constraint on the partitioning columns of a table or uncompressed join index, partitioned access performance can approach the performance of a nonpartitioned table depending on the degree to which the Optimizer can eliminate partitions from consideration by the query.

Access via an equality constraint on a primary index that also includes all the partitioning columns is as efficient as with a nonpartitioned table. If there is an equality constraint on a primary index that does not include all the partitioning columns, but there is an equality or other constraint on the partitioning columns that limits access to a single partition, then access is also as efficient as with a nonpartitioned table.

Access via an equality constraint on the primary index that neither includes the partitioning columns nor constrains the partitioning columns, might not be quite as efficient as with a nonpartitioned table, depending on the number of populated partitions (a partition is said to be populated when it contains rows).

Although you can narrow access by specifying a particular partition set using the PARTITION keyword (see “PARTITION Columns” on page 801), access to particular partitions is generally performed internally and need not be specified explicitly in a request.

 

IF a request …

THEN …

specifies values for all primary index and partitioning columns

a row can be retrieved by single AMP access from a single partition.

specifies values for all primary index columns and also specifies search conditions on the partitioning columns

row partition elimination can reduce the number of combined partitions that must be probed on a particular AMP.

specifies values for all primary index columns but does not specify search conditions on the partitioning columns

each combined partition can be probed individually to locate rows based on their hash value only.

specifies search conditions on the partitioning columns

row partition elimination can reduce an all-AMPs full table scan to an all-AMPs scan of only the combined partitions relevant to the query.

does not specify the values for all primary index columns and there are no constraints on the partitioning columns

the strategy the Optimizer elects to follow depends on whether a usable secondary, hash, or join index exists for the query, as explained by the following bullets.

  • If there is a cost effective secondary, hash, or join index, the Optimizer uses it.
  • If there is no cost effective secondary, hash, or join index, the Optimizer invokes an all‑AMPs full‑table scan.