RE: mystery cartesian join

Thanks for the explanation Kevin.

I was obviously to lazy to RTFM on that. :)

As for the joins, I would still verify that those are being done correctly when joining views.

Jared

                                                                                       
                             
                    "Toepke, Kevin                                                     
                             
                    M"                   To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>        
                    <ktoepke_at_trile       cc:                                           
                             
                    giant.com>           Subject:     RE: mystery cartesian join       
                             
                    Sent by:                                                           
                             
                    root_at_fatcity.c                                                     
                             
                    om                                                                 
                             
                                                                                       
                             
                                                                                       
                             
                    01/16/02 12:04                                                     
                             
                    PM                                                                 
                             
                    Please respond                                                     
                             
                    to 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: 
  INET: Jared.Still_at_radisys.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).