Join Date
Mar 2001
Location
Posts
598

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

2. 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.)

Join Date
Mar 2001
Location
Posts
598
Hi DaPi

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

Thanks

4. 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 . . . .

5. 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.

Join Date
Mar 2001
Location
Posts
598
oh! good
Thanks for ur input

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•