Cartesian Joins in the cloud or not

Is a Cartesian a join method or a join type?  Turns out that most of us have been thinking that MERGE JOIN CARTESIAN was a join method but it’s not.  And even the oracle documentation has it wrong.  This is one of the small morsels of knowledge that came out of Maria Colgan’s training day at the Hotsos Symposium (#hotsym18) this year. 

Most of the time we see MERGE JOIN CARTESIAN in the plan but you can also have a NESTED LOOPS method as a Cartesian as well.  The problem is that the Cartesian option doesn’t show up for the NESTED LOOPS plan.  So it’s not as obvious that it’s a Cartesian results being produced. 

A HASH join cannot be a Cartesian; it requires an equijoin and hence can’t be used to create a Cartesian result. 

So here’s two simple plans showing the good old sort merge and nested loops producing a Cartesian result.

This plan is producing 739,584 rows from my 27,392 employees table and 27 row departments table, which is 27,392 multiplied by 27.  Unlikely this is really the results I want.

Here is the same Cartesian result being produced by a nested loop join method.  Notice a few things:

• Notice the options is blank (it’s the OPTI… column here)
• There is no join predicate (same as sort merge plan)
• LIOs go from 407 to 13,768
• Elapsed time goes from 155,474ms to 200,157ms

Clearly the sort merge method did a lot less work then the nested loops method.  This means we will likely see these more than the nested loop.  And if you do get the nested loops for a method on a Cartesian it’s not as obvious.   Which means you may have some nested loops in your plans producing Cartesian results and not know it.

Maria has logged a documentation bug on this and hopefully we’ll see the documentation correctly identifying this in the future.