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:

NESTED LOOPS: 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.
FILTER: 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:

When the driving (inner) table will return 0 or 1 row. If you have a join query where one of the tables is supplied with the whole of a primary or unique key, Oracle can retrieve the row (if there is one) and then perform a full table scan on the second table. This is more efficient than either a sort-merge or a hash join.
When the outer (second) table is very small (ie. fewer than 100 rows) and can fit into a single block. Since a single block is the smallest amount of data Oracle can read, a Table that fits into a single block can be accessed very fast with a Full Table Scan.
When the outer (second) table is not accessed. ie. All columns required for the SQL can be obtained from the index. This will be obvious in the Explain Plan, because there will be an INDEX SCAN step but no TABLE ACCESS step.
When the outer (second) table is an Index-Organized Table, and is accessed using the Primary Key.
When the outer (second) table is stored in a hash cluster, and is accessed via a Hash Cluster Scan.

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:

For Nested Loop joins where the two tables are both in the FROM clause, add an ORDERED hint and a USE_HASH or USE_MERGE hint. eg:
This will only work for equals joins (use a hash join) or >, <, BETWEEN (use a sort-merge join). If you join with LIKE, != or NOT predicates, Oracle has no choice but to use Nested Loops. Avoid these types of joins at all costs.
For all sub-queries, first of all try an UNNEST hint. This is a very powerful hint that internally restructures your query to make it use a sort-merge or hash join. Take a look at the Explain Plan. If the FILTER step is still there, try the steps below.
For WHERE EXISTS single table sub-queries, try a Semi-Join. If this doesn't work, try rewriting the query as a join

SELECT *
FROM   table_a
WHERE  EXISTS (
       SELECT 1
       FROM   table_b b, table_c c
       WHERE  b.key2 = c.key2
       AND    b.key1 = a.key1);

... becomes ...

SELECT a.*
FROM   table_a a
, (    SELECT DISTINCT b.key1
       FROM   table_b b, table_c c
       WHERE  b.key2 = c.key2
  ) d
WHERE  a.key1 = d.key1

For WHERE NOT EXISTS sub-queries, try re-writing the SQL as an outer join with a b.ROWID IS NULL clause. eg.

SELECT a.*
FROM   table_a a
WHERE  NOT EXISTS (
       SELECT 1
       FROM   table_b
       WHERE  b.col1 = a.col1)

becomes ....

SELECT a.*
FROM   table_a a , table_b b
WHERE  a.col1 = b.col1 (+)
AND    b.rowid IS NULL

For IN and NOT IN sub-queries, eliminate correlated sub-queries, they must use a Nested Loops join. eg.

SELECT a.*
FROM   table_a a
WHERE  col2 IN (
       SELECT col2
       FROM   table_b
       WHERE  b.col1 = a.col1)

becomes ....

SELECT a.*
FROM   table_a a
WHERE  (col1, col2) IN (
       SELECT col2
       FROM   table_b)

For IN sub-queries that are not correlated, try an ORDERED hint and a USE_HASH or USE_MERGE hint. eg:
In this way, Oracle can run the outer query and the sub-query independently and perform a hash join.
For NOT IN sub-queries that are not correlated, try an Anti-Join.

For correlated col = (sub-query) sub-queries, Oracle must use a Filter. Try to re-write as a table join. eg.

SELECT a.*
FROM   table_a a
WHERE  col2 = (
       SELECT max(col2)
       FROM   table_b
       WHERE  b.col1 = a.col1)

becomes ....

SELECT a.*
FROM   table_a a,
(      SELECT col1, max(col2)
       FROM   table_b
       GROUP  BY col1
) b
WHERE  a.col1 = b.col1

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

You may have a Low Volume SQL, or a combination Low and High volume SQL.
Run the original SQL through TK*Prof with EXPLAIN=uid/pwd and check how many rows are processed in the Nested Loops or Filter. If the TK*Prof row counts show zeros, make sure you exited your session before running TK*Prof, or check with the DBA in case that feature has been disabled on your database. If the row count is less than say 4% of the total rows in the second table of the join or filter (and is using an index on that table), then Oracle is probably right to use the nested loops / filter.
One of the other High Volume SQL problems apply in addition to the nested loops problem. Leave the SQL performing a Merge / Hash join and try one of the other fixes.

In summary:

All regular joins should be able to use Hash or Sort Merge, except LIKE, !=, and NOT ... joins.
WHERE EXISTS sub-queries can use Hash or Sort Merge Semi-Joins
WHERE NOT EXISTS will always perform a filter. They must be re-written.
WHERE col IN (sub-query) can use Hash or Sort Merge joins if they are not correlated.
WHERE col NOT IN (sub-query) can use Hash or Sort Merge anti-joins if they are not correlated.
WHERE col = (sub-query) can avoid a Filter only if the sub-query is not correlated.
WHERE [NOT] col { [NOT] != | {>|<}[=] [ANY|ALL] } (sub-query) must use a Filter. Re-write a different way.