page88
70
Join Explosion
Let’s start with a Cartesian product, which can be considered a “join explosion.” In
this section, I will explain what a Cartesian join is, how to identify one, and finally offer
information on how to remediate Cartesian joins.
What Is a Cartesian Join?
A Cartesian join, Cartesian product, cross join, or join explosion occurs when a join
condition is missing from query predicates resulting in every combination of rows for the
absent join condition being returned.
USE WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs );
Suspend and restart the warehouse.
ALTER WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs ) SUSPEND;
ALTER WAREHOUSE IDENTIFIER ( $tpc_warehouse_xs ) RESUME;
You can use the TPC baseline data set to illustrate a Cartesian join output. In the
following example,
region_baseline
has five rows, and
nation_baseline
has 25 rows,
with the join key
regionkey
being omitted. Assuming
regionkey
is matched in both
tables, you should expect 25 rows. As
regionkey
is missing, 125 rows are returned.
SELECT r.r_name AS region_name,
n.n_name AS nation_name
FROM region_baseline r,
nation_baseline n
ORDER BY n.n_name;
Figure 3-14 shows the corresponding query profile. Note that the
CartesianJoin
and
row count are highlighted.
ChapTer 3
The Query profiler