Hash Joins

Overview of Hash Joins
------------------------------------------
•Hash joins are used for joining large data sets.

•The optimizer uses the smaller of two tables or data sources to build a hash table on the join key in memory.

•It then scans the larger table, probing the hash table to find the joined rows.

•This method is best used when the smaller table fits in available memory.

•The cost is then limited to a single read pass over the data for the two tables.

When the Optimizer Uses Hash Joins
----------------------------------------------------
The optimizer uses a hash join to join two tables if they are joined using an equijoin and if either of the following conditions are true:

•A large amount of data needs to be joined.
•A large fraction of a small table needs to be joined.

Hash Join Hints
--------------------------------------------
Apply the USE_HASH hint to instruct the optimizer to use a hash join when joining two tables together.

Hash Join Example:
--------------------------------
You can see the hash join example in my post Use of Hint to use Index