> 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@tce. To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> com> cc: Sent by: Subject: RE: mystery cartesian join [EMAIL PROTECTED] 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: '[EMAIL PROTECTED]' 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@rightn To: "'[EMAIL PROTECTED]'" <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] 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@rightn To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> ow.com> cc: Sent by: Subject: mystery cartesian join [EMAIL PROTECTED] 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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).