Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: 4 join methods?

Re: 4 join methods?

From: Greg Moore <sqlgreg_at_pacbell.net>
Date: Fri, 01 Jun 2001 05:59:41 -0700
Message-ID: <F001.003179F1.20010601053053@fatcity.com>

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US