Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73

    Unanswered: HASH JOIN vs NESTED LOOPS

    Hello,

    Why the query like
    PHP Code:
    SELECT 
      
    FROM A 
        LEFT OUTER JOIN B ON A
    .col1 B.col1 OR A.col2 B.col2 
    is always executed with nested loops, even if I try adding USE_HASH hint?

    Thanks,
    Grzegorz

  2. #2
    Join Date
    Apr 2004
    Posts
    246
    Outer-joins always use nested loops. A hash join needs a row to exist in both tables for the merge to succeed. You want to include data from A that doesn't exist in B, so Oracle knows that a hash join won't work, but a nested loop will be able to keep the driving row (A) even if nothing is found in B.

  3. #3
    Join Date
    Mar 2004
    Location
    Warsaw / Poland
    Posts
    73
    Quote Originally Posted by shoblock
    Outer-joins always use nested loops
    I'm not sure of it:
    Code:
    SQL> SELECT *
      2     FROM A
      3       LEFT OUTER JOIN B ON A.kol1 = B.kol1 AND A.kol2 = B.kol2;
    
    8 rows selected.
    
    Elapsed: 00:00:00.01
    
    Execution Plan
    ----------------------------------------------------------
       0      SELECT STATEMENT Optimizer=CHOOSE (Cost=5 Card=8 Bytes=232)
       1    0   HASH JOIN (OUTER) (Cost=5 Card=8 Bytes=232)
       2    1     TABLE ACCESS (FULL) OF 'A' (Cost=2 Card=8 Bytes=104)
       3    1     TABLE ACCESS (FULL) OF 'B' (Cost=2 Card=4 Bytes=64)
    I think it's rather problem with OR, not with outer join itself...

    Thanks

Posting Permissions

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