Designing Teradata Row Partitioning for Optimal Performance in Tactical Workloads

Teradata is commonly used for tactical workloads and OLTP applications in my projects. However, it is crucial to avoid designing the databases thoughtlessly. Teradata excels as a database for strategic data warehousing.

High-performance queries can often be achieved without special design techniques. Keeping statistics current and correctly designing row partitioning is typically sufficient, while other indexes may not be frequently utilized. In terms of administration effort, strategic workloads require minimal attention. This article will provide guidance for designing Teradata Row Partitioning specifically for tactical workloads.

Teradata Row Partitioning and Tactical Workload

Row partitioning is not a means to optimize a PDM for the tactical workload, but it does facilitate queries by substituting full table scans with superior partition scans.

For tactical workloads, Row Partitioning should be designed to ensure peak performance.

Influence of the Design on the Performance

Row partitioning for tactical queries should use partitioning columns in the WHERE condition to access rows directly, avoiding partition probing. The customer table can be accessed in three ways, with option three offering the same performance as option one (for NPPI tables). Option two should be avoided due to partition probing.

Row Partitioning Recommendations for Tactical Queries

If Primary Index Access for PPI Tables is used for tactical queries, you should consider the following:

• If possible, the PI should be unique
• The partitioning columns should be part of the Primary Index definition

Fulfilling these two conditions ensures PI rows can be located within a single partition.

If the primary index cannot include the partitioning column, it must be added to the WHERE conditions to avoid probing.

Creating a Unique Secondary Index (USI) on the Non-Unique Primary Index (NUPI) columns of the PPI table can prevent probing even if the values are unique. The USI is directly queried, and the base table’s ROWID is utilized to locate each row directly, thus circumventing the more expensive partition probing.