High Volume Nested Loops Joins

Nested Loops joins and Nested Sub-queries are fairly intuitive. The "Nested" term means that someting is performed iteratively: for each A do B. In the case of a Nested Loop join, for each row in table A, lookup matching rows in Table B. For a Nested Sub-Query, for each row in the outer query, execute the sub-query.

This iterative nature is fine when only a few loops are performed, but is generally not scalable to large volumes. The much faster way to handle large data volumes is to use a Hash join or a Sort-Merge join.

In a high volume SQL, you can tell if you are doing a Nested Loops join or Nested Sub-Query by checking the Explain Plan for one of the following:


Usually the result of a table join in the FROM clause of your SQL, but can also appear when you use a WHERE col IN (sub query). Both of these can usually be converted to a Hash join or a Sort-Merge join.


Usually the result of a Nested Sub-Query, but can also sometimes occur when using a View. To tell the difference, count how many component steps are attached to the FILTER step. If there is only one (this is rare), then the FILTER is not a problem; FILTER is just eliminating some rows based on a simple WHERE or HAVING condition. If there are two, then it means Oracle is using the second step to filter rows from the first. ie. The SQL in the second step (usually a sub-query) is run for every row returned from the first step.

The only occasions when a Nested Loops join is acceptable in a high volume SQL are:

Otherwise, if you have a Nested Loops join or a Nested Sub-Query in your high volume SQL, you must convert it to either a Hash join or a Sort-Merge join. Try the following techniques:

Have you eliminated the Nested Loops join or Filter? If so, is performance any better? If not, then one of the following will apply:

In summary:

©Copyright 2003