The Vulnerability of Outer Joins in Business Intelligence Tools: A Simple Test Scenario
Learn why business intelligence tools often use outer joins and why they can be vulnerable to skewing. Read on to understand the details and risks involved.
Business intelligence tools frequently utilize queries with multiple outer joins. This article presents evidence supporting the vulnerability of outer joins to skewing. We will illustrate that, regardless of whether both tables are skewed, there exists a possibility that the plan step in the initial joins may be biased.
Let me outline the details based on a simple test scenario:
CREATE TABLE table1 (pk INTEGER, a INTEGER) PRIMARY INDEX (pk);
CREATE TABLE table2 (pk INTEGER, b INTEGER, c INTEGER) PRIMARY INDEX (pk);
CREATE TABLE table3 (pk INTEGER, d INTEGER, e INTEGER) PRIMARY INDEX (pk);
Want more practical data engineering analysis like this?
Join DWHPro Letters and get field-tested notes on Teradata, Snowflake, AI, migrations, performance, and enterprise data work. DWHPro Letters is free. Subscribe to get new issues by email.
SELECT a,b,c,d,e FROM table1 t01 LEFT JOIN table2 t02 ON t01.a = t02.b LEFT JOIN table3 t03 ON t02.c = t03.d;
SELECT a,b,c,d,e FROM table1 t01 LEFT JOIN table2
The Optimizer can generate an execution plan for the aforementioned setup (tables and query) that consists of the following steps:
1. Redistribute table1 on column a, and redistribute table2 on column b (in parallel).
2. Merge join (outer join) the resulting spools (from step 1). The columns of the resulting spool will be padded with NULL values for columns not matching rows.
2. Merge join (outer join) the resulting spools
3. Let's assume that the resulting spool (i.e., the spool which holds the joined result of table1 and table2) is still not skewed. We will call this spool s(table1,table2) for simplicity.
Get the next issue by email.
4. The result spool s(table1,table2) is redistributed on column c.
5. Table3 is redistributed on column d.
The skewing issues will arise as the join of table1 and table2 may result in numerous non-matching rows, causing multiple NULL values to populate column c of the s(table1,table2).
The rowhash value of a NULL value is deterministic and consistent - every NULL value will always hash to the same AMP.
If only a few rows match during the join of table1 and table2, redistributing s(table1,table2) based on column c (necessary for joining with table3) will relocate any unmatched rows to the AMP holding NULL values.
If only a few rows match during the join of
Initially, our situation was ideal with three unskewed tables. However, executing the second outer join resulted in a skewed spool.
Business intelligence tools often generate queries containing many outer joins, which can significantly increase the likelihood of skewing.
Teradata 14.10 employs techniques such as PDPR to prevent join skewing.
Statistics are essential to ensure the availability of PDPR as the Optimizer may not recognize all applicable situations.
When manually coding queries, only use outer joins when business logic dictates.
Avoid using outer joins unnecessarily to prevent losing rows, which has become a prevalent practice.
Planning or surviving an enterprise data platform migration?
I write regularly about the performance, cost, architecture, and project mistakes that show up in real Teradata, Snowflake, Databricks, and enterprise data work.
Subscribe for free and keep launch access.
Written by Roland Wenzlofsky, founder of DWHPro and author of Teradata Query Performance Tuning. DWHPro has helped data warehouse practitioners for 15+ years.