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 a good 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 big 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 completely overloaded (Hot AMP situation) and may run out of spool space.
Until Teradata Release 13, it was exclusively your task as 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 which would cause 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 a first result set by joining the contents of the redistributed spools.
In a next step, each AMP generates a second result set by joining the contents of the duplicated spool of table S and the local spool of table B.
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 important to keep the statistics fresh.