Hey guys you have not heard about STAR SCHEMA execution plan? -----Original Message----- Sent: Wednesday, January 16, 2002 1:48 PM To: Multiple recipients of list ORACLE-L
This may be a STAR schema execution plan where Oracle joins dimensions and use Nested loop + index to join it to the fact. Some times it's very efficient but if you do not like it I think there is a parameter in init.ora that disables it. Regards, Waleed -----Original Message----- Sent: Wednesday, January 16, 2002 12:10 PM To: Multiple recipients of list 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: Khedr, Waleed 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: Khedr, Waleed 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).