| 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
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-----
From: Kempf, Reed [mailto:rkempf_at_rightnow.com]
Sent: Wednesday, January 16, 2002 2:30 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: mystery cartesian join
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
----------------------------------------- --------
-----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
----------------------------------------- --------
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
------- ------ -------- ---------- ---------- ---------- ----------
Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 18 (SITEMON)
Rows Row Source Operation ------- ---------------------------------------------------1 TABLE ACCESS BY INDEX ROWID SM_LOG_TYPE
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
Rows Execution Plan ------- ---------------------------------------------------2 INDEX GOAL: ANALYZED (UNIQUE SCAN) OF 'SM_LOG_TYPE_PK'
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, ReedReceived on Wed Jan 16 2002 - 14:13:13 CST
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).
 |
 |