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).
