| 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
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
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
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
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'
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
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 - 12:59:49 CST
 |
 |