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

Question

In Oracle, I can use hints USE_HASHor USE_MERGEto instruct the optimizer to make a hash join or a sort-merge join. How do these types of joins differ and when / why should I use one or the other?

Answer 1

Jonathan Lewis posted a really good explanation of how a hash comes together and comes together:

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

and for good measure ...

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

Answer 2

", / "

, . Oracle.

Answer 3

use_hash - . - - .

use_merge /, " ". I.e., A Z.

- - , ,

, , .