page93
75
err_state += "\nMessage : " + err.message;
err_state += "\nStack Trace:\n" + err.StackTraceTxt;
result = err_state;
}
return result;
$$;
With the stored procedure created, you now invoke it with the following:
CALL sp_get_cartesian_join_queries();
Finally, examine the result sets but query the
cartesian_join_queries
table.
SELECT query_id,
operator_type,
operator_id,
operator_attributes,
row_multiple
FROM cartesian_join_queries;
Your results will vary according to the workload carried out before testing. There is
one additional point to note: the
row_multiple
value is always one less than the value
expected, because the first value represents the original row, and all other values are
duplicates.
Cartesian Join and Join Explosion Costs
There are significant impacts from Cartesian joins; we list some of them here:
•
Significantly larger data sets than might be expected are returned.
•
The query runtime is excessively long.
•
They cause spills to disk; I will discuss this later.
•
They consume expensive warehouse resources.
•
They cost you money.
ChapTer 3
The Query profiler