Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: mystery cartesian join
A MERGE CARTESIAN JOIN is one of the new paths the optimizer can take in Oracle 8(i?)
If you really, truely are not missing a join clause, the following may be what is happening....
Basically, what Oracle does is perform a cartesian product (CT) on 2 tables
via a merge-join operation. Generally one of the tables is extremely small
(a couple datablocks or less) Then the resulting CT is joined with the rest
of the tables are joined in. Yes, Virginia, Oracle sometimes voluntarily
performs a CT as part of its optimization logic.
I've seen it be the optimal join path for a large query and I've seen it suck the life out of a query.
HTH
Caver
-----Original Message-----
Sent: Wednesday, January 16, 2002 2:30 PM
To: Multiple recipients of list ORACLE-L
Here is the structure of the underlying base table si_log table. I also do not know what a "MERGE CARTESIAN JOIN" is. The data returned is correct and the tkprof output looks OK except for the elapsed time the query took and the cartesian join portion.
<<snip>>
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Toepke, Kevin M INET: ktoepke_at_trilegiant.com Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-LReceived on Wed Jan 16 2002 - 14:18:56 CST
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).