Subqueries are nested when they appear in the WHERE clause of the parent statement. When an Oracle database evaluates a statement with
a nested subquery, it must evaluate the subquery portion multiple times and may overlook some efficient access paths or joins.
Subquery unnesting unnests and merges the body of the subquery into the body of the statement that contains it, allowing the optimizer to consider
the queries together when evaluating access paths and joins. The optimizer can unnest most subqueries, with some exceptions. Those exceptions
include:
- hierarchical subqueries
- subqueries that contain a
ROWNUM pseudocolumn
- subqueries that contain one of the set operators
- subqueries that contain a nested aggregate function
- subqueries that contain a correlated reference to a query block that is not the immediate outer query block of the subquery.
Assuming no restrictions exist, the optimizer automatically unnests some (but not all) of the following nested subqueries:
- Uncorrelated
IN subqueries
-
IN and EXISTS correlated subqueries, as long as they do not contain aggregate functions or a GROUP BY
clause
You can enable extended subquery unnesting by instructing the optimizer to unnest additional types of subqueries:
- You can unnest an uncorrelated
NOT IN subqueries by specifying the HASH_AJ or MERGE_AJ hint in the
subquery.
- You can unnest other subqueries by specifying the
UNNEST hint in the subquery.
Because these optimizations are dependant on the version of Oracle used, it is best to avoid using nested subqueries in the first place when
possible.
Noncompliant code example
BEGIN
SELECT col1
BULK COLLECT INTO result
FROM table1
WHERE col2 IN (SELECT col3 FROM table2); -- Noncompliant
END;
/
Compliant solution
BEGIN
SELECT col1 -- Compliant
BULK COLLECT INTO result
FROM table1
JOIN table2 ON col2 = col3;
END;
/
