fbpx

More and more often, I experience in projects that Teradata is used as a database for tactical workload or OLTP applications. I can only warn against designing the corresponding databases without much thought. Teradata is an excellent database for strategic data warehousing. Most of the time, you get high-performance queries without applying any special design techniques. It is usually enough to keep the statistics up to date and possibly design row partitioning correctly. Other indexes are useful but are typically not used very often. In general, we can say that the administration effort for the strategic workload is limited. This article will show you what to look for when designing Teradata Row Partitioning for tactical workloads.

Teradata Row Partitioning and Tactical Workload

Row Partitioning is by definition not a tool to design a PDM optimized for the tactical workload. However, it does support queries by replacing full table scans with more efficient partition scans.

Row Partitioning is by definition not a tool to design a PDM optimized for the tactical workload. However, it does support queries by replacing full table scans with more efficient partition scans.

However, especially for tactical workloads, Row Partitioning must be designed to support optimal performance.

Influence of the Design on the Performance

Row partitioning for tactical queries should ideally be designed to use the partitioning columns in the WHERE condition of the queries. This ensures that there is no partition probing and that rows can be accessed directly. The illustration below shows the three different ways to access individual customers in the customer table. Ideal for tactical workloads, option three provides the same performance as option one (which shows access to NPPI tables). Option two causes partition probing and should be avoided:

Teradata Row Partitioning for Tactical Workload 1

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

If these two conditions above are fulfilled, it is ensured that PI rows can be located directly in a single partition.

If it is not possible to include the partitioning column in the primary index, the partitioning column must be included in the WHERE conditions to prevent probing.

If the Primary Index of the PPI table is a NUPI, but the values are unique, probing can be prevented by creating a USI on the NUPI columns. The USI is then queried directly, and the ROWID of the base table can be used to locate each row directly, avoiding the more expensive probing of partitions.

Buy now at Amazon
{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like

>