Oracle always uses HASH JOIN even when both tables are huge?

Hash joins obviously work best when everything can fit in memory. But that does not mean they are not still the best join method when the table can't fit in memory. I think the only other realistic join method is a merge sort join.

If the hash table can't fit in memory, than sorting the table for the merge sort join can't fit in memory either. And the merge join needs to sort both tables. In my experience, hashing is always faster than sorting, for joining and for grouping.

But there are some exceptions. From the Oracle® Database Performance Tuning Guide, The Query Optimizer:

Hash joins generally perform better than sort merge joins. However, sort merge joins can perform better than hash joins if both of the following conditions exist:

  The row sources are sorted already.
  A sort operation does not have to be done.