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:[EMAIL PROTECTED]]
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
----------------------------------------- --------
----------------------------
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: '[EMAIL PROTECTED]'
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@rightn To: "'[EMAIL PROTECTED]'"
<[EMAIL PROTECTED]>
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: [EMAIL PROTECTED]
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@rightn To: Multiple recipients of
list
ORACLE-L <[EMAIL PROTECTED]>
ow.com> cc:
Sent by: Subject: mystery cartesian
join
[EMAIL PROTECTED]
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
------- ------ -------- ---------- ---------- ---------- ----------
----------
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:
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: 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).
