In Oracle, what is the difference between a hash join and a sort-merge join?

In Oracle, I can use the hints USE_HASH or USE_MERGE to inst开发者_开发知识库ruct the optimizer to do a hash join or a sort-merge join. How are those types of joins different and when/why should I use one or the other?

Jonathan Lewis posted a really good explanation of how hash joins and merge joins work:

• Hash Joins - http://jonathanlewis.wordpress.com/2010/08/10/joins-hj/
• Merge Joins - http://jonathanlewis.wordpress.com/2010/08/15/joins-mj/

and for good measure...

• Nested Loop Joins - http://jonathanlewis.wordpress.com/2010/08/09/joins-nlj/

"when/why should I use one or the other"

Generally you shouldn't worry about it. That's what the Oracle optimizer is for.

The use_hash hint requests a hash join against the specified tables. A hash join loads the rows from the left hand table into an in-memory hash table.

The use_merge hint forces a sort/merge operation that essentially does a full table scan and creates a traditional index on the fly. I.e., A to Z.

Because of the memory restrictions on hash joins, you want to use them, generally, only on smaller left hand tables

Sort merge joins are generally best for queries that produce very large result sets or tables that do not possess indexes on the join keys.