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.

__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"62516":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"62516":{"val":"var(--tcb-skin-color-0)"}},"gradients":[]},"original":{"colors":{"62516":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
__CONFIG_colors_palette__{"active_palette":0,"config":{"colors":{"b4fbe":{"name":"Main Accent","parent":-1}},"gradients":[]},"palettes":[{"name":"Default Palette","value":{"colors":{"b4fbe":{"val":"rgb(241, 99, 52)"}},"gradients":[]},"original":{"colors":{"b4fbe":{"val":"rgb(19, 114, 211)","hsl":{"h":210,"s":0.83,"l":0.45}}},"gradients":[]}}]}__CONFIG_colors_palette__
Previous Article
Buy the Book Teradata Query Performance Tuning

Roland Wenzlofsky

Roland Wenzlofsky is an experienced freelance Teradata Consultant & Performance Trainer. Born in Austria's capital Vienna, he is building and tuning some of the largest Teradata Data Warehouses in the European financial and telecommunication sectors for more than 20 years. He has played all the roles of developer, designer, business analyst, and project manager. Therefore, he knows like no other the problems and obstacles that make many data warehouse projects fail and all the tricks and tips that will help you succeed.

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

You might also like