Oracle joins two row sources with one of three methods: nested loops, hash join, or sort-merge join. The method appears as an operation in the execution plan, and the optimizer chooses it from the expected number of rows and the indexes available. A join method that fits the data well can be a hundred times cheaper than one that does not. This guide shows each method in a real plan, explains how it works and when it is chosen, and compares the cost of all three on the same query.
Sample Tables
5,000 customers and 100,000 orders, with an index on the orders' customer ID:
Example:
create table demo_customers (
customer_id number constraint demo_customers_pk primary key,
name varchar2(40) not null,
country varchar2(20) not null);
create table demo_orders (
order_id number constraint demo_orders_pk primary key,
customer_id number not null references demo_customers,
amount number(10,2) not null);
create table demo_amount_bands (
band varchar2(10) not null,
low_amount number not null,
high_amount number not null);
insert into demo_customers
select level, 'Customer ' || level,
decode(mod(level, 4), 0, 'India', 1, 'Germany', 2, 'Brazil', 'Japan')
from dual connect by level <= 5000;
insert into demo_orders
select level, mod(level * 7, 5000) + 1, mod(level * 13, 500) + 10
from dual connect by level <= 100000;
insert into demo_amount_bands values ('SMALL', 0, 99.99);
insert into demo_amount_bands values ('MEDIUM', 100, 299.99);
insert into demo_amount_bands values ('LARGE', 300, 1000);
commit;
create index demo_ord_cust_ix on demo_orders (customer_id);
exec dbms_stats.gather_table_stats(user, 'DEMO_CUSTOMERS')
exec dbms_stats.gather_table_stats(user, 'DEMO_ORDERS')
exec dbms_stats.gather_table_stats(user, 'DEMO_AMOUNT_BANDS')Output:
Table DEMO_CUSTOMERS created. Table DEMO_ORDERS created. Table DEMO_AMOUNT_BANDS created. 5,000 rows inserted. 100,000 rows inserted. 1 row inserted. 1 row inserted. 1 row inserted. Commit complete. Index DEMO_ORD_CUST_IX created. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed.
Nested Loops
A nested loop reads the first row source, the outer or driving one, and for each of its rows looks up the matching rows in the second, the inner one. It suits joins where the outer side returns few rows and the inner side has an index on the join column:
Example:
explain plan for select c.name, o.order_id, o.amount from demo_customers c join demo_orders o on o.customer_id = c.customer_id where c.customer_id in (77, 78); select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');
Output:
Explained.
PLAN_TABLE_OUTPUT
____________________________________________________________________________________
Plan hash value: 1379135957
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 40 | 7 (0)|
| 1 | NESTED LOOPS | | 40 | 7 (0)|
| 2 | NESTED LOOPS | | 40 | 7 (0)|
| 3 | INLIST ITERATOR | | | |
| 4 | TABLE ACCESS BY INDEX ROWID| DEMO_CUSTOMERS | 2 | 3 (0)|
|* 5 | INDEX UNIQUE SCAN | DEMO_CUSTOMERS_PK | 2 | 2 (0)|
|* 6 | INDEX RANGE SCAN | DEMO_ORD_CUST_IX | 1 | 1 (0)|
| 7 | TABLE ACCESS BY INDEX ROWID | DEMO_ORDERS | 20 | 2 (0)|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("C"."CUSTOMER_ID"=77 OR "C"."CUSTOMER_ID"=78)
6 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")
filter("O"."CUSTOMER_ID"=77 OR "O"."CUSTOMER_ID"=78)
21 rows selected.- The two customers are read through the primary key (Ids 4 and 5); they are the outer row source.
- For each customer, Id 6 probes DEMO_ORD_CUST_IX for the matching orders, an access predicate on the join column.
- Two NESTED LOOPS lines are normal: the inner one joins customers to index entries, and the outer one fetches the order rows for those ROWIDs (Id 7). This form lets Oracle batch the table reads.
Hash Join
A hash join reads the smaller row source first and builds a hash table on the join key in memory, then reads the larger one and probes the hash table for every row. It suits large joins on equality conditions, where indexes would mean too many single-row lookups:
Example:
explain plan for select c.country, sum(o.amount) as total from demo_customers c join demo_orders o on o.customer_id = c.customer_id where c.country = 'India' group by c.country; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');
Output:
Explained.
PLAN_TABLE_OUTPUT
_______________________________________________________________________
Plan hash value: 965085934
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25000 | 78 (2)|
| 1 | SORT GROUP BY NOSORT| | 25000 | 78 (2)|
|* 2 | HASH JOIN | | 25000 | 78 (2)|
|* 3 | TABLE ACCESS FULL | DEMO_CUSTOMERS | 1250 | 9 (0)|
| 4 | TABLE ACCESS FULL | DEMO_ORDERS | 100K| 68 (0)|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")
3 - filter("C"."COUNTRY"='India')
17 rows selected.The 1,250 customers in India are the build input (Id 3), and all 100,000 orders are the probe input (Id 4), each read once in a full scan. The total cost is 78.
Sort-Merge Join
A sort-merge join sorts both row sources on the join key and then merges the two sorted lists, much like merging two sorted card decks. Oracle seldom chooses it for equality joins today, because a hash join is usually cheaper; it can win when the rows already come sorted, or for inequality joins. The USE_MERGE hint shows its shape on the same query:
Example:
explain plan for select /*+ leading(c) use_merge(o) */ c.country, sum(o.amount) as total from demo_customers c join demo_orders o on o.customer_id = c.customer_id where c.country = 'India' group by c.country; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');
Output:
Explained.
PLAN_TABLE_OUTPUT
_______________________________________________________________________
Plan hash value: 1508013974
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25000 | 442 (1)|
| 1 | SORT GROUP BY NOSORT| | 25000 | 442 (1)|
| 2 | MERGE JOIN | | 25000 | 442 (1)|
| 3 | SORT JOIN | | 1250 | 10 (10)|
|* 4 | TABLE ACCESS FULL| DEMO_CUSTOMERS | 1250 | 9 (0)|
|* 5 | SORT JOIN | | 100K| 432 (1)|
| 6 | TABLE ACCESS FULL| DEMO_ORDERS | 100K| 68 (0)|
--------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("C"."COUNTRY"='India')
5 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")
filter("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")
20 rows selected.Each input gets a SORT JOIN step. Sorting the 100,000 orders costs most of the 442.
The Same Query with Nested Loops
Forcing nested loops on the large join shows why the optimizer chose the hash join:
Example:
-- the hash join query, forced into nested loops explain plan for select /*+ leading(c) use_nl(o) */ c.country, sum(o.amount) as total from demo_customers c join demo_orders o on o.customer_id = c.customer_id where c.country = 'India' group by c.country; select * from dbms_xplan.display(format => 'BASIC +ROWS +COST +PREDICATE');
Output:
Explained.
PLAN_TABLE_OUTPUT
__________________________________________________________________________________
Plan hash value: 2064518740
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 25000 | 26262 (1)|
| 1 | SORT GROUP BY NOSORT | | 25000 | 26262 (1)|
| 2 | NESTED LOOPS | | 25000 | 26262 (1)|
| 3 | NESTED LOOPS | | 25000 | 26262 (1)|
|* 4 | TABLE ACCESS FULL | DEMO_CUSTOMERS | 1250 | 9 (0)|
|* 5 | INDEX RANGE SCAN | DEMO_ORD_CUST_IX | 20 | 1 (0)|
| 6 | TABLE ACCESS BY INDEX ROWID| DEMO_ORDERS | 20 | 21 (0)|
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("C"."COUNTRY"='India')
5 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")
19 rows selected.1,250 index lookups, each followed by about 20 single-row table reads, cost 26,262: more than 300 times the hash join's 78. The three methods for this query:
- Hash join: 78, chosen by the optimizer.
- Sort-merge join: 442.
- Nested loops: 26,262.
For the two customers of the first example, the order is reversed: nested loops cost 7, because there are only two lookups.
See the Loops in the Actual Statistics
With GATHER_PLAN_STATISTICS, the Starts column shows how often each step ran. ROWSTATS LAST limits the output to the row counts:
Example:
set serveroutput off select /*+ gather_plan_statistics */ max(c.name) as name, max(o.amount) as largest from demo_customers c join demo_orders o on o.customer_id = c.customer_id where c.customer_id in (77, 78); select * from dbms_xplan.display_cursor(format => 'ROWSTATS LAST');
Output:
NAME LARGEST
______________ __________
Customer 78 294
PLAN_TABLE_OUTPUT
___________________________________________________________________________________________
SQL_ID 5w5ddtgmdtm8d, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ max(c.name) as name, max(o.amount)
as largest from demo_customers c join demo_orders o on
o.customer_id = c.customer_id where c.customer_id in (77, 78)
Plan hash value: 3455922067
----------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |
| 2 | NESTED LOOPS | | 1 | 40 | 40 |
| 3 | NESTED LOOPS | | 1 | 40 | 40 |
| 4 | INLIST ITERATOR | | 1 | | 2 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEMO_CUSTOMERS | 2 | 2 | 2 |
|* 6 | INDEX UNIQUE SCAN | DEMO_CUSTOMERS_PK | 2 | 2 | 2 |
|* 7 | INDEX RANGE SCAN | DEMO_ORD_CUST_IX | 2 | 1 | 40 |
| 8 | TABLE ACCESS BY INDEX ROWID | DEMO_ORDERS | 40 | 20 | 40 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - access(("C"."CUSTOMER_ID"=77 OR "C"."CUSTOMER_ID"=78))
7 - access("O"."CUSTOMER_ID"="C"."CUSTOMER_ID")
filter(("O"."CUSTOMER_ID"=77 OR "O"."CUSTOMER_ID"=78))
Note
-----
- this is an adaptive plan
33 rows selected.- The index range scan on the inner side started 2 times, once per customer, and returned 40 index entries in total.
- The table access by ROWID started 40 times, once per order.
- E-Rows is an estimate per start, while A-Rows is the total of all starts, so compare A-Rows with E-Rows multiplied by Starts.
Things to Know
- In a nested loop, the outer row source is the upper child; in a hash join, the upper child is the build input. Both should be the smaller side.
- A nested loop over many outer rows is a common cause of slow queries: look for a large Starts value on the inner side.
- Hash joins need memory for the hash table; a build input that does not fit spills to the temporary tablespace.
- Hints such as LEADING, USE_NL, USE_HASH, and USE_MERGE are good for comparing plans. USE_NL alone was not enough here: the LEADING hint had to fix the join order first.
Related Guides
- How to Read an Execution Plan in Oracle
- How to Read Table and Index Access Paths in Oracle Execution Plans
Conclusion
Nested loops are best for few outer rows with an indexed inner side, hash joins for large equality joins, and sort-merge joins for sorted or inequality inputs. Read the join method together with the row estimates and the Starts column: a nested loop that starts thousands of times usually wants to be a hash join.
