Oracle Database - Hash joins
Table of Contents
1 - About
Hash joins are join operation 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.
2 - Articles Related
3 - 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.
4 - Example
The table orders is used to build the hash table, and order_items is the larger table, which is scanned later.
SELECT o.customer_id, l.unit_price * l.quantity FROM orders o ,order_items l WHERE l.order_id = o.order_id; -------------------------------------------------------------------------- | Id | Operation | Name | ROWS | Bytes | Cost (%CPU)| -------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 665 | 13300 | 8 (25)| |* 1 | HASH JOIN | | 665 | 13300 | 8 (25)| | 2 | TABLE ACCESS FULL | ORDERS | 105 | 840 | 4 (25)| | 3 | TABLE ACCESS FULL | ORDER_ITEMS | 665 | 7980 | 4 (25)| -------------------------------------------------------------------------- Predicate Information (IDENTIFIED BY operation id): --------------------------------------------------- 1 - access("L"."ORDER_ID"="O"."ORDER_ID")
5 - Hash Join Hints
Apply the USE_HASH hint to instruct the optimizer to use a hash join when joining two tables together.