DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Merge Join Cartesian

  1. #1
    Join Date
    Mar 2001
    Location
    Reading, U.K
    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
    Attached Files Attached Files
    Cheers!
    OraKid.

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.)

  3. #3
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    Hi DaPi

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



    Thanks
    Cheers!
    OraKid.

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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. #5
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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.

  6. #6
    Join Date
    Mar 2001
    Location
    Reading, U.K
    Posts
    598
    oh! good
    Thanks for ur input
    Cheers!
    OraKid.

Posting Permissions

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


Click Here to Expand Forum to Full Width