| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: mystery cartesian join
are the joins themselves correct?
-----Original Message-----
From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com]
Sent: Wednesday, January 16, 2002 1:21 PM
To: Multiple recipients of list ORACLE-L
Subject: Re: mystery cartesian join
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,
FROM v_si_last_faq_log vslfl,
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,
FROM v_si_last_faq_log vslfl,
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
------- ------ -------- ---------- ---------- ---------- ----------
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'
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).Received on Wed Jan 16 2002 - 13:06:31 CST
 |
 |