HASH JOIN vs NESTED LOOPS

HrabiaGrzegorz · #1 (**permalink**) 06-22-04, 11:25

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

shoblock · #2 (**permalink**) 06-22-04, 11:40

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.

HrabiaGrzegorz · #3 (**permalink**) 06-23-04, 03:01

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

Internet Services

Web Development

Digital Marketing

Consumer Tech