page90
72
FROM partsupp_baseline ps,
part_baseline p,
supplier_baseline s
WHERE ps.ps_partkey = p.p_partkey;
As every SQL developer will attest, it’s easy to miss a join condition particularly
where composite natural keys are used. A query profile provides the means to spot
these conditions, and Figure 3-15 shows a
CartesianJoin
clearly indicated for our
example query.
Figure 3-15.
Cartesian join tabular output
As previously stated, profiling every SQL statement as part of our continuous
integration testing and then scanning the output for the
CartesianJoin
keyword would
capture this particular problem before delivery.
Identifying Cartesian Joins
In a production system you might not immediately know which query is causing a
Cartesian join, particularly after a new software release where a robust continuous
integration practice has not been implemented. In this scenario, you are looking to
identify rogue queries from all of those queries that have been executed.
Let’s first create a table to hold all candidate query IDs for later investigation. I leave
it to you to add timestamps, etc.
CREATE OR REPLACE TABLE cartesian_join_queries
(
sp_name STRING,
query_id STRING,
ChapTer 3
The Query profiler