fbpx

Dealing with data skew can be considered a principal activity in Teradata performance optimization. Although skew on table level usually can be avoided by choosing a proper Primary Index, skew arising during query execution is often a major issue (so-called spool skew).

Spool skew is often emerging when joining tables (or spools). On a typical Teradata System,  the join process is executed in parallel by all available AMPs at the same time.

Let’s consider the following join example:

B … Big table with 100 Million rows

S … Small table with 10.000 rows

We assume that both tables are joined on a column, which is neither the primary index of B nor the primary index of S. In such a case, the optimizer may choose to rehash and redistribute both tables. Rehashing is the right approach if the join column values if both tables are evenly distributed across all AMPs.

But we assume that the big table B has the same value in the join column in about 99% of the rows, i.e., 99 Million of rows out of 100 Million rows carry the same value in the join column.

As of now, we run into a significant performance problem caused by skew.

By rehashing the rows, 99 Million rows are distributed to only one AMP (as they carry the same hash value).

The receiving AMP will be overloaded entirely (Hot AMP situation) and may run out of spool space.

Until Teradata Release 13, it was exclusively your task as a performance specialist to solve suchlike dynamic skewing problems by forcing the optimizer into a different execution plan.
Sometimes this could be solved by adding statistics. Some of the time, a complete rewrite of the SQL statement was the only opportunity.

Starting with Teradata 14,  a sophisticated method for resolving such data skew issues was implemented.

Each AMP receives information about skewed values (in our example, this is the big table B).

Biased rows of the big table B stay locally on their AMPs. Only rows not causing skew are redistributed by rehashing over the join column(s).

Similarly, not biased rows from the small table S are rehashed on their join column(s), but rows that would skew after redistribution are duplicated to all AMPs.

As soon as the partial redistribution and partial duplication of the tables have been completed (PRPD), each AMP generates the first result by joining the redistributed spools’ contents.

In the next step, each AMP generates a second result set by joining the duplicated spool contents of table S and table B’s local spool.

Starting with Teradata 14, PRPD relives the performance specialist from some skew related problems, but the Teradata optimizer needs to know about biased values. Hence it is crucial to keep the statistics fresh.

__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
__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__
Next 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.

  • how do we know when the optimizer is going for this PRPD ? is there any specific keyword which we can see in explain plan when PRPD takes place?

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

    You might also like

    >