Every join in a plan is done one of three ways:
| Method | How | Good when |
|---|---|---|
| Nested loops | for each row of the outer input, look up its matches in the inner (usually through an index) | the outer input is small and the inner has an index on the join column |
| Hash join | read the smaller input into a hash table in memory, then stream the bigger one past it | big inputs, equality joins, no useful index |
| Merge join | sort both inputs on the join key, then walk them side by side | inputs already sorted, or non-equality joins (<, BETWEEN) |
A real plan with two hash joins
The 2026 top-five run-scorers, joining deliveries, matches and players. On our 19c Autonomous database:
- 17SELECT STATEMENT5 rows
- 16SORT ORDER BY5 rows
- 15VIEW5 rows
- 14WINDOW SORT PUSHED RANK72 rows
- 13PX COORDINATOR
- 12PX SEND QC (RANDOM) :TQ1000172 rows
- 11WINDOW CHILD PUSHED RANK72 rows
- 10HASH GROUP BY72 rows
- 9PX RECEIVE72 rows
- 8PX SEND HASH :TQ1000072 rows
- 7HASH GROUP BY72 rows
- 6HASH JOIN3658 rows
- 1TABLE ACCESS FULL CR_PLAYERS72 rows
- 5HASH JOIN3658 rows
- 2TABLE ACCESS FULL CR_MATCHES16 rows
- 4PX BLOCK ITERATOR7551 rows
- 3TABLE ACCESS FULL CR_DELIVERIES7551 rows
Numbers are the order the steps run: children before their parent, top child first. Rows flow up the tree.
The DBMS_XPLAN text
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 16 (25)|
| 1 | SORT ORDER BY | | 5 | 16 (25)|
| 2 | VIEW | | 5 | 15 (20)|
| 3 | WINDOW SORT PUSHED RANK | | 72 | 15 (20)|
| 4 | PX COORDINATOR | | | |
| 5 | PX SEND QC (RANDOM) | :TQ10001 | 72 | 15 (20)|
| 6 | WINDOW CHILD PUSHED RANK | | 72 | 15 (20)|
| 7 | HASH GROUP BY | | 72 | 15 (20)|
| 8 | PX RECEIVE | | 72 | 15 (20)|
| 9 | PX SEND HASH | :TQ10000 | 72 | 15 (20)|
| 10 | HASH GROUP BY | | 72 | 15 (20)|
| 11 | HASH JOIN | | 3658 | 12 (0)|
| 12 | TABLE ACCESS FULL | CR_PLAYERS | 72 | 2 (0)|
| 13 | HASH JOIN | | 3658 | 10 (0)|
| 14 | TABLE ACCESS FULL | CR_MATCHES | 16 | 2 (0)|
| 15 | PX BLOCK ITERATOR | | 7551 | 8 (0)|
| 16 | TABLE ACCESS FULL| CR_DELIVERIES | 7551 | 8 (0)|
-----------------------------------------------------------------------------Follow the numbers: read the 72 players into a hash table (1), read the 16 matches of 2026 into
another (2), stream all 7,551 deliveries past the matches (3-5) and keep the 3,658 from 2026,
stream those past the players (6), group by player in two parallel stages (7-10), keep the top 5
(11 and 14, PUSHED RANK: it never sorts all 72 players, it just keeps the best five as it goes),
and sort those five (16).
The same query on 26ai
- 17SELECT STATEMENT5 rows
- 16COUNT STOPKEY
- 15VIEW66 rows
- 14SORT ORDER BY STOPKEY66 rows
- 13HASH GROUP BY66 rows
- 12MERGE JOIN66 rows
- 2TABLE ACCESS BY INDEX ROWID CR_PLAYERS72 rows
- 1INDEX FULL SCAN CR_PLAYERS_PK72 rows
- 11SORT JOIN66 rows
- 10VIEW VW_GBC_1166 rows
- 9HASH GROUP BY66 rows
- 8HASH JOIN3658 rows
- 5JOIN FILTER CREATE :BF000016 rows
- 4TABLE ACCESS BY INDEX ROWID BATCHED CR_MATCHES16 rows
- 3INDEX RANGE SCAN CR_MATCHES_UK16 rows
- 7JOIN FILTER USE :BF00007551 rows
- 6TABLE ACCESS STORAGE FULL CR_DELIVERIES7551 rows
Numbers are the order the steps run: children before their parent, top child first. Rows flow up the tree.
The DBMS_XPLAN text
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 26 (31)|
| 1 | COUNT STOPKEY | | | |
| 2 | VIEW | | 66 | 26 (31)|
| 3 | SORT ORDER BY STOPKEY | | 66 | 26 (31)|
| 4 | HASH GROUP BY | | 66 | 26 (31)|
| 5 | MERGE JOIN | | 66 | 22 (19)|
| 6 | TABLE ACCESS BY INDEX ROWID | CR_PLAYERS | 72 | 2 (0)|
| 7 | INDEX FULL SCAN | CR_PLAYERS_PK | 72 | 1 (0)|
| 8 | SORT JOIN | | 66 | 20 (20)|
| 9 | VIEW | VW_GBC_11 | 66 | 19 (16)|
| 10 | HASH GROUP BY | | 66 | 19 (16)|
| 11 | HASH JOIN | | 3658 | 18 (12)|
| 12 | JOIN FILTER CREATE | :BF0000 | 16 | 2 (0)|
| 13 | TABLE ACCESS BY INDEX ROWID BATCHED| CR_MATCHES | 16 | 2 (0)|
| 14 | INDEX RANGE SCAN | CR_MATCHES_UK | 16 | 1 (0)|
| 15 | JOIN FILTER USE | :BF0000 | 7551 | 15 (7)|
| 16 | TABLE ACCESS STORAGE FULL | CR_DELIVERIES | 7551 | 15 (7)|
-----------------------------------------------------------------------------------------------Same answer, very different route. The 26ai optimizer:
- groups before joining to players (
VW_GBC_11, "group-by placement"): sum the runs perbatter_idfirst, so only 66 rows reach the players join instead of 3,658; - joins those 66 to the players with a merge join, reading players in key order through their primary key index (no sort needed on that side);
- builds a Bloom filter (
JOIN FILTER CREATE :BF0000) from the 2026 matches and applies it while scanning deliveries, so most 2025 balls are thrown away inside the Exadata storage layer; - stops at five rows (
COUNT STOPKEY).
Neither plan is "the" plan: they're two optimizers' best guesses for two different databases.
The test case
Ask for each join method with a hint, and check which join operation appears in each plan.
Setup
delete from plan_table where statement_id like 'D142%';Setup
explain plan set statement_id = 'D142_HASH' for
select /*+ leading(m d) use_hash(d) */ count(*)
from cr_matches m join cr_deliveries d on d.match_id = m.match_id
where m.season = 2026;Setup
explain plan set statement_id = 'D142_NL' for
select /*+ leading(m d) use_nl(d) index(d cr_deliveries_pk) */ count(*)
from cr_matches m join cr_deliveries d on d.match_id = m.match_id
where m.season = 2026;Setup
explain plan set statement_id = 'D142_MERGE' for
select /*+ leading(m d) use_merge(d) */ count(*)
from cr_matches m join cr_deliveries d on d.match_id = m.match_id
where m.season = 2026;Expected result
| METHOD | HASH_JOIN | NESTED_LOOPS | MERGE_JOIN |
|---|---|---|---|
| HASH | Y | N | N |
| MERGE | N | N | Y |
| NL | N | Y | N |
select substr(statement_id, 6) as method,
max(case when operation = 'HASH JOIN' then 'Y' else 'N' end) as hash_join,
max(case when operation = 'NESTED LOOPS' then 'Y' else 'N' end) as nested_loops,
max(case when operation = 'MERGE JOIN' then 'Y' else 'N' end) as merge_join
from plan_table
where statement_id like 'D142%'
group by statement_id
order by method;The hints: LEADING(m d) says "start from matches, then deliveries"; USE_HASH(d), USE_NL(d) and
USE_MERGE(d) say how to join d in. Hints are for experiments like this one; in production code
they're the last resort.
19c and 26ai
The same join methods on both; 26ai's optimizer has more transformations (group-by placement, more Bloom filters) and picks differently more often.
Gotchas
- A NESTED LOOPS over two big inputs is the classic slow plan: usually caused by a bad row estimate ("1 row" that is really a million). Check the estimates first.
- Hash joins need memory. When the hash table doesn't fit, it spills to temp and slows down.
- Two
NESTED LOOPSlines in a row in modern plans are normal: it's nested-loop batching, one loop for the index and one for the table.
Part 14 quiz
5 questions on all of Part 14. Get 4 right to pass. Retake it as often as you like.