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: mystery cartesian join

RE: mystery cartesian join

From: <Jared.Still_at_radisys.com>
Date: Wed, 16 Jan 2002 15:21:07 -0800
Message-ID: <F001.003F2114.20020116150029@fatcity.com>

> Try putting in an ORDERED hint and see if that helps.

Good call!

The following link is a discussion of a similar nature, and the cartesian product
was eliminated through the use of the 'ordered' hint.

http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_database_id=FOR&p_id=126013.999

Jared

                                                                                       
                             
                    Thomas Jeff                                                        
                             
                    <ThomasJe_at_tce.       To:     Multiple recipients of list ORACLE-L 
<ORACLE-L_at_fatcity.com>        
                    com>                 cc:                                           
                             
                    Sent by:             Subject:     RE: mystery cartesian join       
                             
                    root_at_fatcity.c                                                     
                             
                    om                                                                 
                             
                                                                                       
                             
                                                                                       
                             
                    01/16/02 11:56                                                     
                             
                    AM                                                                 
                             
                    Please respond                                                     
                             
                    to ORACLE-L                                                        
                             
                                                                                       
                             
                                                                                       
                             




Maybe the optimizer thinks there's a many-to-many relationship between si_log
and sm_monitor? Try putting in an ORDERED hint and see if that helps.

Jeff T.

-----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.

desc si_log

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 ERROR_ID                                  NOT NULL NUMBER (PK)
 ERROR_TYPE_ID                             NOT NULL NUMBER(8) (FK) to
sm_log_type slt
 EWHEN                                     NOT NULL DATE
 INTERFACE_ID                              NOT NULL NUMBER (FK) to
si_interfaces si
 REQUEST_TIME                                       NUMBER
 LAST_LOG                                           VARCHAR2(1)
 FAQ_REQUEST_TIME                                   NUMBER
 LAST_FAQ_LOG                                       VARCHAR2(1)


ReedK

-----Original Message-----
Sent: Wednesday, January 16, 2002 12:23 PM To: 'ORACLE-L_at_fatcity.com'
Cc: Kempf, Reed

Reed,

What is the structure of the SI_LOG table (the one being accessed by the view).

Furthermore, what is a "MERGE JOIN CARTESIAN" - anybody? I am thinking that
this is normal, in that the query was broken up into two distinct parts, and
the results of those parts should be "MERGE JOIN CARTESIAN" to get the correct results.

Anybody?

Tom Mercadante
Oracle Certified Professional

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

Reed,

Please reply to the list as well.

I don't seen any key information here.

And taking a closer I caught the part about this join being done on views.

Joining views can be problemetic. You need to ensure that the joins in a view ( if any ) are correct, and that all columns of the primary key are available from the view.

These columns then need to be used to fully qualify the join.

Jared

                    "Kempf, Reed"


                    <rkempf_at_rightn       To:
"'Jared.Still_at_radisys.com'"
<Jared.Still_at_radisys.com>
                    ow.com>              cc:


                                         Subject:     RE: mystery cartesian

join
                    01/16/02 10:36


                    AM














Jared,

Thanks for the quick response. Here is some more information. The problem

is that the query is taking 1.6 seconds to complete when I think it should take milliseconds to complete.

Here are the structures of the 2 tables. I check my indexes and keys and all appear to OK.

desc si_monitor (the interface_id is a foreign key) This table has 1500 rows in it and does not grow that fast.

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 INTERFACE_ID                                       NUMBER(10)
 ERROR_CHECK                                        VARCHAR2(1)
 ERROR_TIME                                         DATE
 MONITOR                                            VARCHAR2(1)
 TIMEOUT_VAL                                        NUMBER(3)
 BROKEN_OK                                          VARCHAR2(1)


desc sm_log_type (the error_type_id is the primary key) This table has a static 14 rows in it.

 Name                                      Null?    Type
 ----------------------------------------- --------
----------------------------
 ERROR_TYPE_ID                             NOT NULL NUMBER(8)
 SHORTNAME                                 NOT NULL VARCHAR2(30)
 WARN_TIME                                          NUMBER
 ERROR_TIME                                         NUMBER
 WARN_EMAIL                                         VARCHAR2(4000)
 ERROR_EMAIL                                        VARCHAR2(4000)
 WARN_TEXT                                          VARCHAR2(4000)
 ERROR_TEXT                                         VARCHAR2(4000)
 WARN                                               VARCHAR2(1)
 F_WARN                                             VARCHAR2(1)
 DESCRIPTION                                        VARCHAR2(250)


Thanks again.

ReedK

-----Original Message-----
Sent: Wednesday, January 16, 2002 11:21 AM To: ORACLE-L_at_fatcity.com
Cc: Kempf, Reed

It's impossible to precisely determine where the cartesian product is coming from with out knowing the primary keys of the si_monitor and sm_log_type slt tables.

If your join does not include all columns of the parent table(s), there is the possibility of a cartesian product. ( it's data dependant )

Jared

                    "Kempf, Reed"


                    <rkempf_at_rightn       To:     Multiple recipients of
list
ORACLE-L <ORACLE-L_at_fatcity.com>
                    ow.com>              cc:


                    Sent by:             Subject:     mystery cartesian
join
                    root_at_fatcity.c


                    om








                    01/16/02 09:10


                    AM


                    Please respond


                    to ORACLE-L












Hello gurus,

I have a query which seems to have a mystery cartesian join in it and I can't seem to locate it. I would definitely appreciate some help if possible. My view doesn't have a join associated with it and it appears that I have the correct number of joins for the number of tables I am selecting from. I have also recently rebuilt my indexes and analyzed all tables. One other point is that I am joining across schemas by way of synonyms. The si_monitor table resides in a separate schema. Hmmmm?

Here is my query:

SELECT vslfl.error_type_id error_type_id,

        vslfl.interface_id,
        sm.error_check,
       86400 * (sysdate - sm.error_time) err_secs,
        slt.warn,
        slt.shortname
    FROM v_si_last_faq_log vslfl,
        si_monitor sm,
        sm_log_type slt

    WHERE vslfl.interface_id = sm.interface_id     AND vslfl.error_type_id = slt.error_type_id     AND vslfl.interface_id = 1
/

Here is the output from the tkprof:

SELECT vslfl.error_type_id error_type_id,

        vslfl.interface_id,
        sm.error_check,
       :"SYS_B_0" * (sysdate - sm.error_time) err_secs,
        slt.warn,
        slt.shortname
    FROM v_si_last_faq_log vslfl,
        si_monitor sm,
        sm_log_type slt

    WHERE vslfl.interface_id = sm.interface_id     AND vslfl.error_type_id = slt.error_type_id     AND vslfl.interface_id = :"SYS_B_1"

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ----------



Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.71 1.68 3114 3540 4 1
------- ------ -------- ---------- ---------- ---------- ----------

total 4 0.71 1.68 3114 3540 4 1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18 (SITEMON)

Rows Row Source Operation

-------  ---------------------------------------------------
      1  NESTED LOOPS
      2   MERGE JOIN CARTESIAN
      2    VIEW V_SI_LAST_FAQ_LOG
      2     SORT ORDER BY
      1      TABLE ACCESS BY INDEX ROWID SI_LOG
   3629       INDEX RANGE SCAN (object id 3281)
      2    SORT JOIN
      1     TABLE ACCESS FULL SI_MONITOR
      1   TABLE ACCESS BY INDEX ROWID SM_LOG_TYPE
      2    INDEX UNIQUE SCAN (object id 3318)





Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      1   NESTED LOOPS
      2    MERGE JOIN (CARTESIAN)
      2     VIEW OF 'V_SI_LAST_FAQ_LOG'
      2      SORT (ORDER BY)
      1       TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
                  'SI_LOG'
   3629        INDEX   GOAL: ANALYZED (RANGE SCAN) OF
                   'SI_LOG_INT_IDX' (NON-UNIQUE)
      2     SORT (JOIN)
      1      TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'SI_MONITOR'
      1    TABLE ACCESS   GOAL: ANALYZED (BY INDEX ROWID) OF
               'SM_LOG_TYPE'
      2     INDEX   GOAL: ANALYZED (UNIQUE SCAN) OF 'SM_LOG_TYPE_PK'
                (UNIQUE)


Here is the syntax from my view (v_si_last_faq_log):

SELECT error_id last_error_id,

        interface_id,
        error_type_id,
        ewhen,
        request_time

FROM si_log
WHERE last_faq_log = 'Y'
ORDER BY error_id
/

Any help would be appreciated.

Thanks in Advance

ReedK

--
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
--------------------------------------------------------------------
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).
--
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
--------------------------------------------------------------------
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).
Received on Wed Jan 16 2002 - 17:21:07 CST

Original text of this message

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