Oracle Database - Hash joins

Hash Join

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.

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")

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

• Joins HJ