This article presents and tries to explain joins between two tables that can be encountered in Oracle: HASH, NESTED, MERGE

HASH join

Hash join operation consists of two steps in following order:

•  reads <outer table> and builds hash table in memory (PGA area, temp in case it’s big)

•  reads data from <inner table> and finds matching records in hashed <outer table>

----------------------------------------------
| Id  | Operation          | Name        
----------------------------------------------
|   0 | SELECT STATEMENT   |             
|   1 |  HASH JOIN         |             
|   2 |   TABLE ACCESS FULL| <outer table>  <- hash table in memory
|   3 |   TABLE ACCESS FULL| <inner table>
-----------------------------------------------

This kind of join is the most popular in warehouses and enables to join very effectively large tables, sets of rows. <inner table> is quite often called “driving table” – table which searches data in other table. <outer table> which is hashed in memory you can treat as MEMORY INDEX.

NESTED join

Nested join operation consists of one step :

• reads <outer table> and for each record it tries to find records in <inner table>. Usually it searches data in <inner table> using already existing index <inner table index>

-------------------------------------------------------
| Id  | Operation                    | Name            
-------------------------------------------------------
|   0 | SELECT STATEMENT             |                 
|   1 |  NESTED LOOPS                |                 
|   2 |   TABLE ACCESS FULL          | <outer table>  
|   4 |   TABLE ACCESS BY INDEX ROWID| <inner table>
|   5 |    INDEX RANGE SCAN          | <inner table index>
--------------------------------------------------------

In this join “driving table” is <outer table> in contrast to HASH join where driving table was <inner table>.

As you can see in HASH join Oracle builds index for you in MEMORY and in NESTED join it tries to use if possible physical index on database. If you don’t have index for NESTED join on <inner table> it’s time to worry unless you join really small number of records.

This kind of join is very popular in OLTP systems where you join small number of records.

MERGE join

Merge join operation consists of three steps in following order:

• reads and sorts <outer table> based on join key

• reads and sorts <inner table> based on join key

• merge sorted results

------------------------------------------
| Id  | Operation           | Name        
------------------------------------------
|   0 | SELECT STATEMENT    |             
|   1 |  MERGE JOIN         |             
|   2 |   SORT JOIN         |             
|   3 |    TABLE ACCESS FULL| <outer table>
|   4 |   SORT JOIN         |             
|   5 |    TABLE ACCESS FULL| <inner table>
------------------------------------------

It’s not so popular type of join but you can see it especially if sorting operations 1 or 2 can be skipped. Example of such nosort operation is reading physical index.

Have a fun 🙂

Tomasz