Hash Join & Merge Join

Merge Join :

Oracle performs a join between two sets of row data using the merge

join algorithm. The inputs are two separate sets of row data. Output is

the results of the join. Oracle reads rows from both inputs in an

alternating fashion and merges together matching rows in order to

generate output. The two inputs are sorted on join column.

Hash Join :

Oracle performs a join between two sets of row data using hash join

algorithm. Input and Output same as Merge Join. Oracle reads all rows

from the second input and builds a hash structure (like has table in

java), before reading each row from the first input one at a time. For

each row from the first input, the hash structure is probed and matching

rows generate output.