| 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
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: '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 ----------------------------------------- --------
----------------------------
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 ListsReceived on Wed Jan 16 2002 - 13:43:49 CST
--------------------------------------------------------------------
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).
 |
 |