December 24, 2010
Imagine that a developer approached you with the following SQL statement, and explained that he (or she) noticed that the execution plan showed a Cartesian merge join. Even adding an ORDERED hint did not affect the execution plan, and the execution performance was the same with the ORDERED hint.
SELECT X.TIME_PERIOD EXPOSURE_PERIOD,Y.TIME_PERIOD EVALUATION_PERIOD,BUSINESS_UNIT, TO_NUMBER(LOB_VALUE) BUSINESS_UNIT_LOB_ID_FIN,0 CALC_VALUE FROM ACTUARIAL_REF_DATA.TIME_PERIOD_HIERARCHY X,ACTUARIAL_REF_DATA.TIME_PERIOD_HIERARCHY Y,ANALYSIS_BUSINESS_UNITS, ANALYSIS_LOBS WHERE X.TIME_PERIOD BETWEEN TO_NUMBER('200001') AND TO_NUMBER('200212') AND Y.TIME_PERIOD BETWEEN TO_NUMBER('200001') AND TO_NUMBER('200212') AND BUSINESS_UNIT = '33011';
Cartesian joins are not automatically a sign of problems. Previous blog articles have demonstrated that a Cartesian join may be more efficient than other methods for certain situations (see Improving Performance by Using a Cartesian Join and Improving Performance by Using a Cartesian Join 2), the execution plan may simply display a Cartesian join rather than a nested loops join when one of the row sources is expected to return a single row, or the optimizer may introduce a Cartesian join as a transformation due of transitive closure (see this article).
How would you help the developer with the above SQL statement? Would you first attempt to reformat the SQL statement, or would you do something else as the first step (note that this blog article is not specifically interested in just helping the developer with this one SQL statement, but instead is seeking advice to help the developer know what to do the next time)? Think about the SQL statement for a moment…
Almost a year ago I put together another blog article (SQL Basics – Working with ERP Data) that was based on a small portion of a presentation that I gave to an ERP user’s group. I almost created a new blog article that basically stated the same tips shortly after I saw the above SQL statement in this OTN thread (please do not visit the thread until you have had a chance to think about the SQL statement, and how you would help the developer).