# Merge Join Cartesian

• 11-12-2004, 05:41 AM
balajiyes
Merge Join Cartesian
Hi All

Can someone explain about "MERGE JOIN CARTESIAN"

SELECT STATEMENT, GOAL = CHOOSE Cost=7 Cardinality=1 Bytes=287
TABLE ACCESS BY INDEX ROWID Object owner=EDW_2_3_0 Object name=C_REFERENCE Cost=2 Cardinality=1 Bytes=67
NESTED LOOPS Cost=7 Cardinality=1 Bytes=287
NESTED LOOPS Cost=5 Cardinality=1 Bytes=220
MERGE JOIN CARTESIAN Cost=4 Cardinality=1 Bytes=120
TABLE ACCESS FULL Object owner=EDW_2_3_0 Object name=IMEI_DEALER Cost=2 Cardinality=1 Bytes=102
BUFFER SORT Cost=2 Cardinality=82 Bytes=1476
TABLE ACCESS FULL Object owner=EDW_2_3_0 Object name=VOUCHER_PAYMENT Cost=2 Cardinality=82 Bytes=1476
TABLE ACCESS BY INDEX ROWID Object owner=EDW_2_3_0 Object name=PIVOT_SUBSCRIBER_DETAIL Cost=1 Cardinality=1 Bytes=100
INDEX UNIQUE SCAN Object owner=EDW_2_3_0 Object name=PK_PIVOT_SUBSCRIBER_DETAIL Cardinality=35776
INDEX RANGE SCAN Object owner=EDW_2_3_0 Object name=C_REFERENCE_TYPE_ID_IDX Cost=1 Cardinality=98

Thanks
• 11-12-2004, 06:01 AM
DaPi
My understanding is that if a join will be made between two sets where at least one has VERY low number of rows, the CBO makes a Cartesian Join (Product) and then throws out the rows that don't meet the join condition.

In your case the CBO thinks the join is between sets of 1 and 82 rows each.

This can go horribly wrong if the stats on the tables aren't good.

(Of course you can get a Cartesian Product - and possibly blow your temp TS - if you forget the join condition!

If you didn't know already - a Cartesian Product of two sets is all combinations of rows from the two sets - can be very big.)
• 11-12-2004, 06:29 AM
balajiyes
Hi DaPi

Will the results be incorrect? I mean the output of the query?

Thanks
• 11-12-2004, 07:11 AM
DaPi
If it's coded right, the answer will be correct.

If it's a question of the CBO taking this rather unexpected option, the answer should be correct. (If not raise a TAR.)

If it's a question of omitting a join condidtion (we've all done that!) well . . . .
• 11-12-2004, 07:15 AM
DaPi
Quote:

Originally posted by DaPi
This can go horribly wrong if the stats on the tables aren't good.
By this I mean that the query could be slow (or VERY slow) - the answer should (will) be OK.
• 11-12-2004, 07:50 AM
balajiyes
oh! good
Thanks for ur input