Star query uses it, but was not mentioned in the original post.
The query in the original post also does not look like a star
join to me.
The following is also from MetaLink.
Doing cartesian products is normal expected behavior from the CBO as it
sometimes is cheaper (in CBO terms and also the real world) to place two
(or more)
non-joined row sources early in the join sequnce - thus giving the merge
join cartesian operation. As an example this is what the star join is all
about.
Jared
"Khedr,
Waleed" To: Multiple recipients of list ORACLE-L
<[EMAIL PROTECTED]>
<Waleed.Khedr@ cc:
FMR.COM> Subject: RE: mystery cartesian join
Sent by:
[EMAIL PROTECTED]
om
01/16/02 12:35
PM
Please respond
to ORACLE-L
Hey guys you have not heard about STAR SCHEMA execution plan?
-----Original Message-----
Sent: Wednesday, January 16, 2002 1:48 PM
To: Multiple recipients of list ORACLE-L
This may be a STAR schema execution plan where Oracle joins dimensions and
use Nested loop + index to join it to the fact.
Some times it's very efficient but if you do not like it I think there is a
parameter in init.ora that disables it.
Regards,
Waleed
-----Original Message-----
Sent: Wednesday, January 16, 2002 12:10 PM
To: Multiple recipients of list 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: Khedr, Waleed
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: Khedr, Waleed
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).
