DWHPro Insights

Optimizing Teradata Joins with Skewed Data: Strategies and Solutions

By Roland Wenzlofsky ·

Optimizing Teradata Joins with Skewed Data: Strategies and Solutions

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.

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_IDCUSTOMER_NAMECURRENCY_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_CDCURRENCY_NAME
EUREuro
USDUS Dollar
AUDAustralian Dollar
HUFHungarian 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.

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

Our 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_IDCUSTOMER_NAMECURRENCY_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.

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.

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