RE: mystery cartesian join

From: Kempf, Reed <rkempf_at_rightnow.com>
Date: Wed, 16 Jan 2002 13:07:25 -0800
Message-ID: <F001.003F1DAD.20020116125159@fatcity.com>

Thanks for the insight. I will rewrite my query to not use the view and see if the optimizer is more friendly with the execution plan.

Thanks also to SteveO for the rewrite idea.

ReedK
DBA from Montana

-----Original Message-----
Sent: Wednesday, January 16, 2002 1:04 PM To: Multiple recipients of list ORACLE-L

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-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Kempf, Reed
INET: rkempf_at_rightnow.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------