Most people think of cartesian product as a type of
SQL coding that produces a result set where all rows in one table are joined to
all rows in another. This could be accomplished by any of the three
commonly known join methods. For example,
select ename
, dname
from emp
, dept
/
Execution Plan
----------------------
SELECT STATEMENT Optimizer=CHOOSE
NESTED LOOPS
TABLE ACCESS (FULL) OF 'DEPT'
TABLE ACCESS (FULL) OF 'EMP'
Above a cartesian product SQL is resolved by a nested loops join method.
select ename
, dname
from emp
, dept
/
Execution Plan
----------------------
SELECT STATEMENT Optimizer=CHOOSE
NESTED LOOPS
TABLE ACCESS (FULL) OF 'DEPT'
TABLE ACCESS (FULL) OF 'EMP'
Above a cartesian product SQL is resolved by a nested loops join method.
I notice in explain plans a sort merge step appears
as:
...
MERGE JOIN
...
However, in 8.1.6, and possibly before, Oracle may resolve a query against a large child table and two small parent tables by first performing a cartesian product on the smaller tables, then joining the result set with the large table. This is invoked with the STAR hint and may happen without the hint if you happen to join one large table with two smaller lookup tables. In this case the step where the smaller tables are joined appears as:
...
MERGE JOIN (CARTESIAN)
...
This does not happen because the SQL is written as a cartesian product, and the results do not reflect a join of all rows to all rows. The cartesian product is merely an intermediate step used to optimize processing.
My question is whether the MERGE JOIN
(CARTESIAN) step is a separate join method. Certainly it is
not the normal processing for what is typically understood to be SQL that
creates a cartesian product, which could be resolved with any of the three
ordinary join methods seen in explain plans.
I have searched the Oracle documentation and find no explanation for the difference between an explain plan step MERGE JOIN, which we know is a sort merge method, and a step MERGE JOIN (CARTESIAN), which as far as I can tell is never mentioned in the documentation, and which apparently represents the mysterious fourth method!
Does anyone know where I can find any further information on MERGE JOIN (CARTESIAN)?
