page89
71
Figure 3-14.
Cartesian join query profile
A valid, but rare, use case for a Cartesian join is when generating test data—lots of
test data. Selecting from two tables without a join condition results in the number of
rows returned from the first table multiplied by the number of rows in the second table.
To demonstrate a Cartesian product using
EXPLAIN
, let’s create a suboptimal SQL
statement by excluding one join condition from the previous example query used to
create
v_supplier_part
.
EXPLAIN USING TABULAR
SELECT p.p_name AS part_name,
p.p_retailprice AS retail_price,
ps.ps_supplycost AS supply_cost,
ps.ps_availqty AS available_quantity,
ps.ps_supplycost / ps.ps_availqty AS unit_price,
s.s_acctbal AS supplier_account_balance,
s.s_name AS supplier_name
ChapTer 3
The Query profiler