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, <FONT
face=Courier>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: <FONT
face=Courier>... 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 <FONT
face=Courier>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)?
Received on Fri Jun 01 2001 - 07:59:41 CDT