Introduction to Teradata Partial Redistribution and Partial Duplication

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 significant 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 out of 100 Million rows carry the same value in the join column.

As of now, we have 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. Sometimes, 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 tables’ partial redistribution and partial duplication 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 relieves 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.