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

Question

In Oracle, I can use the hints USE_HASH or USE_MERGE to instruct 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?

Answer 1

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/