DWHPro Insights

How to Reduce Skew in Teradata Joins Using the RANDOM() Function

By Roland Wenzlofsky ·

How to Reduce Skew in Teradata Joins Using the RANDOM() Function

Natural skew may hinder join performance, but the PRPD capability in Teradata 14 can alleviate this problem. However, PRPD may not detect all potential applications if skewed values are not evident in the statistics.

If your Teradata version is below 14, PRPD is unavailable. To address this, you can use a technique based on the RANDOM() function to reduce skew.

Teradata Joins are vulnerable to Skewing.

Let’s walk through the specifics step by step.

Our example involves a join between the two tables shown below. Certain facts are already established about these tables:

random
  1. Table 1 contains no rows with ‘A’ in the join column. Furthermore, it’s ensured that the value ‘A’ will never be added to this column.
  2. The join column of Table 2 contains many rows with the value ‘A’. It’s skewed (our example tables only include a few rows, but that’s enough to get the idea).
  3. Both tables are evenly distributed across all AMPs on their primary index column (called “primary key” in our example).
  4. Reserve replacement strings that cannot occur in the opposite join input, Table1. Preserve non-A values and NULLs. If the output exposes the replaced join column, keep the original value separately to preserve the projection as well as matching semantics.

The above facts are necessary for our testing framework to operate.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

By performing a left join of Table 1 and Table 2 on the common column, both tables’ rows are hashed on this column. Assuming Teradata redistributes both tables, this is exemplified.

The informed reader understands Teradata’s architecture and will recognize that all rows containing ‘A’ in the join column will be relocated to a single AMP, causing Table 2’s spool to be imbalanced. Conversely, the redistribution of Table 1 should yield an evenly distributed spool. When joining, the AMP holding Table 2’s rows with ‘A’ will bear a greater workload than the other AMPs.

We can use the RANDOM() function to prevent skewing in this scenario.

Table 1 does not contain any rows with the value ‘A’ in the join column, meaning there will be no matches for any row in Table 2 containing ‘A’.

We can easily replace ‘A’ in Table 2 with a range of values unavailable as regular data (see requirement four above). This is where the RANDOM() function comes into play.

We can generate a range of values, such as TRIM(CAST(RANDOM(1,100) AS VARCHAR(3))), which replace the ‘A’ values of Table 2. As these values are non-existent in regular data, this will not affect the result set:

CREATE VOLATILE TABLE NOT_SKEWING_TABLE2
AS
(
SELECT PrimaryKey, CASE WHEN JoinColumn = 'A' THEN TRIM(CAST(RANDOM(1,100) AS VARCHAR(3))) ELSE JoinColumn END AS JoinColumn FROM Table2
) WITH DATA PRIMARY INDEX (PrimaryKey) ON COMMIT PRESERVE ROWS;

Random replacement can spread a biased key but cannot guarantee uniform AMP balance. The historical four-hour to three-minute result was not reproduced. Validate collisions, unmatched rows, NULLs, output columns and actual plan/resource use before applying this transformation.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

3 thoughts on “How to Reduce Skew in Teradata Joins Using the RANDOM() Function”

  1. Select ……………… from table1
    left outer join table2
    on
    case when table2.Joincolumn = ‘A’ then cast(random(1,100) as varchar(3))
    else table1.Joincolumn = table2.Joincolumn end:

    Reply
  2. Instead of using the Volatile table, the quick way to get rid of skew value is to add a filter condition in the join clause as “Table2.JoinColumn ‘A’ “, since Table 2 is an inner table only matching records will be projected from this table.

    This optimization usually applies when the outer table has skewed values in join columns. Example:

    Select ……………… from table1 left outer join table2 on
    table1.Joincolumn = table2.Joincolumn;

    Let’s consider the table1 join column has a particular skewed value like ‘A’ which does not exist in table2. We cannot filter out this value as table1 is an outer table and all matching and non-matching values need to be projected.

    We can solve this skew issue using below random() function technique,

    Select ……………… from table1 left outer join table2 on
    case when table1.Joincolumn = ‘A’ then cast(random(1,100) as varchar(3)) else table1.Joincolumn = table2.Joincolumn;

    Reply

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam