17.10 - Single-AMP Queries and Partitioned Tables - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)

Primary index row-partitioning refers to the physical ordering of rows within the table. Partitioned rows are grouped on each AMP based on a partitioning key, which can be one or multiple columns (see Partitioned and Nonpartitioned Primary Indexes for details). The partitioning columns need not be part of the primary index; however, when the partitioning column is different from the primary index, the performance of tactical queries that pass only a primary index value might be affected.

When the system performs a primary index access to a row-partitioned table and the query does not provide the partitioning key in a condition, each row partition might need to be probed individually to determine if it contains rows reflecting that value. As seen in the following graphic, if you specify a partitioning key value in a WHERE condition (in this case, the condition is c_date = ‘2006-03-15’), the Optimizer can use row partition elimination to probe only the row partition that contains the sought after value.



Probing always occurs when the primary index is nonunique because of the possibility that duplicate primary index rows with different partitioning key values might be spread across the table. The primary index of a partitioned table must be defined as nonunique unless its partitioning key is also included as part of the primary index definition (see Row-partitioned Primary Indexes). When the partitioning key is included as part of the primary index definition, the system does not need to probe each individual row partition.