Teradata Joins and Skewing

0
448
teradata access paths

A skewed workload is the number one performance issue. The Optimizer tries to avoid it by applying techniques such as PDPR.

The following example shows how the data move strategy is adjusted to the skew factor of the tables participating in a merge join. In this situation, sample statistics of a few percent would not be sufficient. The Optimizer would choose a bad join strategy.

The test setup is based on a join between two tables. Primary index of both tables is the column PK but join column of the left table is “Col6”. Therefore it’s a must to recalculate the rowhash of the left table over the column COL6.

The left table has 6 Million rows. The right table contains 500,000 rows:

SELECT * FROM TEST_DB.left_table main
INNER JOIN TEST_DB.TABLE2 right_table
ON main.COL6 = right_table.PK
WHERE right_table.COL4 = ‘A’;

In our first test, both tables are evenly distributed across all AMPs:

4) We do an all-AMPs RETRIEVE step from TEST_DB.main by way of an
all-rows scan with no residual conditions into Spool 2 (all_amps),
which is redistributed by the hash code of (TEST_DB.main.COL6)
to all AMPs.  Then we do a SORT to order Spool 2 by row hash.  The
size of Spool 2 is estimated with high confidence to be 6,708,141
rows (7,184,419,011 bytes).  The estimated time for this step is 1
minute and 46 seconds.
5) We do an all-AMPs JOIN step from TEST_DB.right_table by way of a
RowHash match scan with a condition of (“TEST_DB.right_table.COL4 = ‘A
‘”), which is joined to Spool 2 (Last Use) by way of a RowHash
match scan.  TEST_DB.right_table and Spool 2 are joined using a merge
join, with a join condition of (“COL6 = TEST_DB.right_table.PK”).  The
result goes into Spool 1 (group_amps), which is built locally on
the AMPs.  The size of Spool 1 is estimated with low confidence to
be 6,708,141 rows (28,422,393,417 bytes).  The estimated time for
this step is 31.20 seconds.

The Optimizer redistributes the left table (about 6 million rows) by the rowhash of COL6 to all AMPs; in a next step, a merge join is done between the spool of the left and the right table. Note that the right table is joined directly without spooling.

The above-described strategy is solid. As both tables are evenly distributed across the AMPs, the workload of all AMPs will be the same.

What will happen if we change the data demographics of the right table by locating 30% of the rows on exactly one AMP?

With collected statistics available the Optimizer will choose to duplicate the right table. This way it avoids a skewed join step:

4) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from TEST_DB.right_table by way
of an all-rows scan with a condition of (
“TEST_DB.right_table.COL4 = ‘A ‘”) into Spool 2 (all_amps), which
is duplicated on all AMPs.  Then we do a SORT to order Spool
Two by the hash code of (TEST_DB.right_table.PK).  The size of
Spool 2 is estimated with high confidence to be 3,600,000
rows (3,855,600,000 bytes).  The estimated time for this step
is 34.56 seconds.
2) We do an all-AMPs RETRIEVE step from TEST_DB.main by way
of an all-rows scan with no residual conditions into Spool 3
(all_amps), which is built locally on the AMPs.  Then we do a
SORT to order Spool 3 by the hash code of (
TEST_DB.main.COL6).  The size of Spool 3 is estimated with
high confidence to be 6,708,141 rows (7,184,419,011 bytes).
The estimated time for this step is 45.73 seconds.
5) We do an all-AMPs JOIN step from Spool 2 (Last Use) by way of a
RowHash match scan, which is joined to Spool 3 (Last Use) by way
of a RowHash match scan.  Spool 2 and Spool 3 are joined using a
merge join, with a join condition of (“COL6 = PK”).  The result
goes into Spool 1 (group_amps), which is built locally on the AMPs.
The result spool file will not be cached in memory.  The size of
Spool 1 is estimated with low confidence to be 3,354,070,500 rows
(14,211,196,708,500 bytes).  The estimated time for this step is 3
hours and 26 minutes.

The Optimizer duplicates the right table to all AMPs and sorts the spool by the rowhash of the column PK. At the same time – in parallel – the left table is locally spooled on each AMP. The spool is sorted by the rowhash of the column COL6.

The final step is the merge joins.

By duplicating the right table, uneven workload during the joining step is avoided.

When dealing with skewed data, random-AMP samples is not enough, and the Optimizer will make wrong decisions for joins.

Our Reader Score
[Total: 9    Average: 3.9/5]
Teradata Joins and Skewing written by Roland Wenzlofsky on June 2, 2015 average rating 3.9/5 - 9 user ratings

LEAVE A REPLY

Please enter your comment!
Please enter your name here