Home » SQL & PL/SQL » SQL & PL/SQL » Merge Join Cartesian (Oracle 9)
- Merge Join Cartesian [message #351020] Mon, 29 September 2008 00:48 Go to next message
richardh
Messages: 3
Registered: September 2008
Junior Member
Hello,

I've a small question. The following code gives me an MERGE JOIN CARTESIAN

*********** ** ** **** **
select distinct vzd.nr_rel_vzd nr_rel_vzd
, vzd.nr_bron nr_bron
from (select nvl(max(ins_nr),0) max_ins_nr from eur_fct_nat_pers where id_bron = (select max(nr_bron) from eur_el_vzd where rownum = 1)) fct
, eur_el_vzd vzd
, eur_el_rel_adr_click rac
where vzd.ind_fict = 'N'
and vzd.nr_rel_vzd = rac.nr_rel(+)
and vzd.nr_rol_vzd = rac.nr_rol_rel(+)
and vzd.nr_bron = rac.nr_bron_rel(+)
and (vzd.upd_nr >= fct.max_ins_nr or vzd.ins_nr >= fct.max_ins_nr or
rac.upd_nr >= fct.max_ins_nr or rac.ins_nr >= fct.max_ins_nr)
*********** ** ** **** **
EXPLAIN PLAN

SELECT STATEMENT, GOAL = CHOOSE Cost=10985 Cardinality=53808 Bytes=3067056
SORT UNIQUE Cost=10985 Cardinality=53808 Bytes=3067056
FILTER
HASH JOIN OUTER
MERGE JOIN CARTESIAN Cost=2086 Cardinality=124750 Bytes=4491000
VIEW Object owner=DV_SOFT Cost=1056 Cardinality=1 Bytes=13
SORT AGGREGATE Cardinality=1 Bytes=8
PARTITION RANGE SINGLE
PARTITION HASH ALL
TABLE ACCESS FULL Object owner=MART Object name=EUR_FCT_NAT_PERS Cost=1056 Cardinality=133955 Bytes=1071640
SORT AGGREGATE Cardinality=1 Bytes=4
COUNT STOPKEY
TABLE ACCESS FULL Object owner=DV_EL Object name=EUR_EL_VZD Cost=1030 Cardinality=249500 Bytes=998000
TABLE ACCESS FULL Object owner=DV_EL Object name=EUR_EL_VZD Cost=1030 Cardinality=124750 Bytes=2869250
TABLE ACCESS FULL Object owner=DV_EL Object name=EUR_EL_REL_ADR_CLICK Cost=667 Cardinality=242117 Bytes=5084457
*** ** **** *** *** ***

This is because the where conditions has an OR statement in it.

I can't get this Merge Join Cartesian out. I've tried a union (and loose the OR statement) but this is not good for the performance. I've already joint all tabels in the WERE clause.

Anyone got an good suggestion?

regards,
Richard

Report message to a moderator

- Re: Merge Join Cartesian [message #351025 is a reply to message #351020] Mon, 29 September 2008 01:09 Go to previous messageGo to next message
manojkb
Messages: 16
Registered: March 2006
Location: Dubai
Junior Member
richardh wrote on Mon, 29 September 2008 09:48
=(select max(nr_bron) from eur_el_vzd where rownum = 1)


May I know why you use where caluse here with rownum.

Report message to a moderator

- Re: Merge Join Cartesian [message #351040 is a reply to message #351025] Mon, 29 September 2008 01:33 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
You are Missing some join conditions.

Regards,
Rajat Ratewal

Report message to a moderator

- Re: Merge Join Cartesian [message #351043 is a reply to message #351040] Mon, 29 September 2008 01:54 Go to previous messageGo to next message
Frank
Messages: 7901
Registered: March 2000
Senior Member
Don't mix up a merge join cartesian with a cartesian product.
A merge join cartesian is not (necessarily) bad.

Report message to a moderator

- Re: Merge Join Cartesian [message #351045 is a reply to message #351020] Mon, 29 September 2008 01:57 Go to previous messageGo to next message
richardh
Messages: 3
Registered: September 2008
Junior Member
Ofcourse you may know why the "=(select max(nr_bron) from eur_el_vzd where rownum = 1)" is in it Wink

this returns an ID (we have multipe databases to extract data from), for the Datamarts we want to join a record with others but having the same Schema (id).

Rownum=1 is perhaps not nessesarily but it gives us a better record.

regards,
Richard

Report message to a moderator

- Re: Merge Join Cartesian [message #351046 is a reply to message #351043] Mon, 29 September 2008 02:00 Go to previous messageGo to next message
richardh
Messages: 3
Registered: September 2008
Junior Member
Oww... that's true indeed... I think I'm confusing the Cartesion product with a Merge on cartesian.

Thats why I found it strange a saw a cartesian but connected all input tables with eachother.

thks... for the tip.

Regards,
Richard

Report message to a moderator

- Re: Merge Join Cartesian [message #351052 is a reply to message #351046] Mon, 29 September 2008 02:18 Go to previous messageGo to next message
rajatratewal
Messages: 507
Registered: March 2008
Location: INDIA
Senior Member
Quote:

Frank Wrote On:Mon, 29 September 2008 01:54

Don't mix up a merge join cartesian with a cartesian product.
A merge join cartesian is not (necessarily) bad.



Why??
Frank can you please clarify why it's not cartesian product when we miss ceratin join condition.

I always thought that this is very expensive opearation
and should be avoided.

Please chk this URL it clearly says:-

http://www.dba-oracle.com/t_sql_merge_join_cartesian.htm

The use of a merge join cartesian is very expensive to Oracle, and are only appropriate in rare cases where one of the tables has a very small number of rows.

Regards,
Rajat

Report message to a moderator

- Re: Merge Join Cartesian [message #351060 is a reply to message #351052] Mon, 29 September 2008 02:42 Go to previous message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
MERGE JOIN CARTESIAN has a bad reputation from the days of the Rule Based Optimizer because it usually signalled a programming error, and was the cause of a performance problem. Under the Cost Based Optimizer, MERGE JOIN CARTESIAN is often used to join two unrelated tables where one table will return just a single row (or no rows). A cartesian join is only a problem if both row sources in the join have a large number of rows.
Ross Leishman

Report message to a moderator

Previous Topic: Decode Statement OR SubQuery
Next Topic: Update Query (merged)
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Apr 28 05:20:46 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.