| 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)
|
|
|
|
| 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?
|
|
|
|
| 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
|
|
|
|
| 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?
|
|
|
|
| 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
|
|
|
|
| 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 |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
