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

CURRENCY TABLE

To join with Column CURRENCY_CD, ensure that this column is the primary index for both tables and that the rows exist on the relevant AMPs.

Our table shows that most accounts lack a currency assignment, resulting in a skewed join.

Despite the optimizations implemented in the latest Teradata versions to mitigate the issue, such as re-hashing evenly distributed values while duplicating NULL rows to all AMPs, the problem persists.

This is particularly true when the optimizer lacks current statistics on biased values.

A Possible Solution To This Problem

Replacing the NULL value with a highly distinct value is a viable solution.

In our case, for example, the CUSTOMER_ID can be used. We could cast it to a CHARACTER column, add a “special string’ in front of it, and write it into the Column CURRENCY_CD:

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.