OraFAQ Forum: Performance Tuning » Why Merge Cartesian Join

Home » RDBMS Server » Performance Tuning » Why Merge Cartesian Join

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Why Merge Cartesian Join [message #159017]

Wed, 15 February 2006 10:16

mjschwenger
Messages: 137
Registered: July 2005
Location: US

Senior Member

I have a query which reports Merge Cartesian Join in the trace although I cannot explain why.
Thanks a lot for the help.
mj

SELECT er_state.*, er_history.sys_create_dt
FROM er_state, er_history
WHERE er_state.er_id = er_history.er_id
AND er_state.er_id = :1
AND er_state.ent_id = :2

CREATE INDEX IX_ER_STATE ON ER_STATE(ER_ID, ENT_ID,ACCT_ID);

ALTER TABLE ER_HISTORY ADD CONSTRAINT PK_ER_HISTORY_ID PRIMARY KEY(ER_ID) USING INDEX REVERSE;

Rows Row Source Operation
------- ---------------------------------------------------
1 MERGE JOIN CARTESIAN (cr=4 pr=0 pw=0 time=284 us)
1 TABLE ACCESS BY INDEX ROWID ER_STATE (cr=2 pr=0 pw=0 time=136 us)
1 INDEX RANGE SCAN IX_ER_STATE (cr=1 pr=0 pw=0 time=58 us)(object id 15946)
1 BUFFER SORT (cr=2 pr=0 pw=0 time=136 us)
1 TABLE ACCESS BY INDEX ROWID ER_HISTORY (cr=2 pr=0 pw=0 time=49 us)
1 INDEX UNIQUE SCAN PK_ER_HISTORY_ID (cr=1 pr=0 pw=0 time=26 us)(object id 15947)

Report message to a moderator

Re: Why Merge Cartesian Join [message #159037 is a reply to message #159017]

Wed, 15 February 2006 15:21

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

In your post you showed us that er_id was the pk of er_history via the constraint. But you didn't do the same for your other table. It is a natural assumption that it would be, but is it? How about foreign key?

Report message to a moderator

Re: Why Merge Cartesian Join [message #159085 is a reply to message #159037]

Thu, 16 February 2006 01:06

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Cartesian joins are not only used where you don't provide a join condition. Oracle usually chooses a CARTESIAN when its statistics or a unique key tell it that either of the tables will retrieve 0 or 1 row.

Since you are providing a UNIQUE key to ER_HISTORY, it returns a single row. Even if the other table returns 1,000 rows, the cartesian only produces 1,000 result rows.

Cartesian is actually the fastest way to resolve a join where one side has 0 or 1 rows. The CBO is right to choose it.

_____________
Ross Leishman

Report message to a moderator

Re: Why Merge Cartesian Join [message #159182 is a reply to message #159017]

Thu, 16 February 2006 09:45

smartin
Messages: 1803
Registered: March 2005
Location: Jacksonville, Florida

Senior Member

Hey Ross, do you have an example showing the cartesian used in the "non cartesian join" sense, or a link to an article or paper or anything where I can learn more about it? Do I just need to reread the performance tuning guide?

Report message to a moderator

Re: Why Merge Cartesian Join [message #159184 is a reply to message #159017]

Thu, 16 February 2006 10:27

mjschwenger
Messages: 137
Registered: July 2005
Location: US

Senior Member

Thanks Ross,
you are right. The query is quite fast and has no problems but a colleague of mine saw the "Merge Cart Join" and told me I have a problem. To prove your point for the same query, SQL server does outer join reference index seek on the U Key.

Thanks a lot,mj

Report message to a moderator

Re: Why Merge Cartesian Join [message #159264 is a reply to message #159184]

Fri, 17 February 2006 01:26

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Sorry Scot. Like so much of the nonsense that spouts from my keyboard, it is based on personal experience.

Like the OP, I first noticed Oracle doing CJs on me and panicked a bit (this was way back on 7.3!). I did some experimentation and - although a CJ with one table having 0 or 1 row is functionally very similar to a NL join, Oracle still prefers the CJ.

Note that a cartesian join is called "MERGE JOIN CARTESIAN" by the optimizer. This may be a clue. Perhaps internally it uses a different algorithm to the NL join that slighlty more efficient.

Honestly though, if you forced it to do a NL join with the 1-row table as the driving table, then I reckon you'd strugle to detect a performance difference.
_____________
Ross Leishman

[Updated on: Fri, 17 February 2006 01:27]

Report message to a moderator

Previous Topic:	No data in v$sql_plan for SELECT
Next Topic:	functional index and bind variable problem

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat May 03 04:24:42 CDT 2025