Skewed Teradata Joins – The Initial Situation
Consider the scenario where one table includes various currencies while the other comprises customers’ accounts with their corresponding currency. Essentially, the ISO code of the currency serves as a foreign key in the account table.
Part of the Teradata Physical Design and Data Modelling guide.
CREATE TABLE Currency
(
CURRENCY_CD VARCHAR(20) NOT NULL,
CURRENCY_NAME VARCHAR(200)
) PRIMARY INDEX (CURRENCY_CD);
CREATE TABLE Customer
(
CUSTOMER_ID INTEGER NOT NULL,
CUSTOMER_NAME VARCHAR(255),
CURRENCY_CD CHAR(20)
) PRIMARY INDEX (CUSTOMER_ID);
CUSTOMER TABLE
| CUSTOMER_ID | CUSTOMER_NAME | CURRENCY_CD |
| 1 | Nina Lowery | EUR |
| 2 | Alexia Neal | USD |
| 3 | Kyla Chan | NULL |
| 4 | Alesha Ferrell | NULL |
| 5 | Cara Adams | NULL |
| 6 | Abigail Larsen | NULL |
| 7 | Amie Massey | NULL |
CURRENCY TABLE
| CURRENCY_CD | CURRENCY_NAME |
| EUR | Euro |
| USD | US Dollar |
| AUD | Australian Dollar |
| HUF | Hungarian Forint |
Joining on CURRENCY_CD does not require both tables to use it as PI. The optimizer can redistribute or duplicate inputs; the corrected minimal fixture joined with the original differing PIs.
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 bookOur table shows that most accounts lack a currency assignment, resulting in a skewed join.
PRPD handles biased and nonbiased join keys using appropriate partial redistribution/duplication. It is not a general rule to duplicate skewed NULL rows, especially when equality joins do not match NULLs.
This is particularly true when the optimizer lacks current statistics on biased values.
A Possible Solution To This Problem
Prefer a scoped query transformation that preserves stored NULLs and unmatched rows. Sentinels must be collision-free and must not leak into the output; replacing stored NULL changes IS NULL, grouping, constraints and application behavior.
| CUSTOMER_ID | CUSTOMER_NAME | CURRENCY_CD |
| 1 | Nina Lowery | EUR |
| 2 | Alexia Neal | USD |
| 3 | Kyla Chan | #NULL#3 |
| 4 | Alesha Ferrell | #NULL#4 |
| 5 | Cara Adams | #NULL#5 |
| 6 | Abigail Larsen | #NULL#6 |
| 7 | Amie Massey | #NULL#7 |
However, using this approach renders NULL values unidentifiable and requires additional query logic.
Want to gain comprehensive knowledge about joins in Teradata? Read this article.
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.
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