Difference between nested loop and hash join?
In the world of Oracle SQL tuning, the battle between Nested Loops and Hash Joins is a classic. Choosing the right one is often the difference between a query that finishes in milliseconds and one that runs for hours.
Think of it as the difference between looking up names in a phone book one by one versus building a temporary digital database to find matches instantly.
A Nested Loop join is the most intuitive method. It works exactly like a nested FOR loop in programming.
Oracle picks a Driving Table (the "Outer" table).
For every single row found in the driving table, it goes and looks for a match in the second table (the "Inner" table).
Data Volume: Small to medium datasets.
Indexing: You have a highly efficient index on the join column of the inner table.
Environment: OLTP (Online Transaction Processing) where users need the "First Rows" quickly.
Analogy: You have a list of 10 student names. You walk into a library and look up each name, one by one, in the card catalog. It's very fast because you only have 10 names.
The Hash Join is the heavy lifter of modern databases. It was designed to handle large volumes of data where indexes might be missing or inefficient.
Oracle takes the smaller of the two tables and builds a Hash Table in memory (specifically in the PGA).
It then "probes" this hash table by scanning the second (larger) table once.
As it reads each row of the large table, it calculates a hash value and checks the memory-resident hash table for a match.
Data Volume: Large datasets.
Indexing: No indexes available, or the query needs to process a huge percentage of the table.
Environment: Data Warehousing or batch processing where "Total Throughput" matters more than the first row.
Analogy: You take the smaller list of students and put them into a high-speed digital scanner. Then you slide the entire school's master list through the scanner. The machine beeps every time it sees a match. You only walk through the master list once.
| Feature | Nested Loops (NL) | Hash Join (HJ) |
| Logic | Iterative (Row by Row). | Set-based (Hashed in Memory). |
| Ideal For | Small result sets. | Large result sets. |
| Index Dependency | Highly Dependent on indexes. | Independent of indexes. |
| Memory Usage | Very low. | High (Requires PGA space). |
| First Row Speed | Very Fast. | Slow (Must build hash table first). |
| Total Throughput | Slow for large data. | Very Fast for large data. |
The Oracle Optimizer (CBO) picks between these based on Cardinality Estimates.
The Nested Loop Trap: If Oracle thinks there is only 1 row in the driving table (but there are actually 1 million), it will pick a Nested Loop. Because there is no index, it will perform 1 million full table scans on the second table. This is a "query killer."
The Hash Join Trap: If you are only looking for 5 rows, building a massive hash table in memory is a waste of time and resources. A Nested Loop would have finished before the Hash Join even finished "prepping" the memory.
When you run an EXPLAIN PLAN, look at the Operation column:
NESTED LOOPS: You'll see the driving table first, followed by an index lookup or table access for the inner table.
HASH JOIN: You'll see two tables being scanned, with the "Hash Join" operation connecting them.
If you are in a reporting tool and you want the first page of data to appear instantly, you can "nudge" the optimizer toward Nested Loops using the /*+ FIRST_ROWS */ hint. If you are running a massive end-of-month report, the /*+ FULL */ or /*+ USE_HASH(table_a table_b) */ hints are your friends.