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:
INDEX SCANstep but no
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:
SELECT /*+ ORDERED USE_HASH(b)*/ * FROM table_a a, table_b b WHERE ...
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
SELECT a.* FROM table_a a WHERE NOT EXISTS ( SELECT 1 FROM table_b WHERE b.col1 = a.col1)
SELECT a.* FROM table_a a , table_b b WHERE a.col1 = b.col1 (+) AND b.rowid IS NULL
SELECT a.* FROM table_a a WHERE col2 IN ( SELECT col2 FROM table_b WHERE b.col1 = a.col1)
SELECT a.* FROM table_a a WHERE (col1, col2) IN ( SELECT col2 FROM table_b)
SELECT /*+ ORDERED USE_HASH(a)*/ * FROM table_a a WHERE col1 IN ( SELECT col1 FROM table_b b)
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.
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)
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:
EXPLAIN=uid/pwdand 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.