Subqueries are nested when they appear in the
WHERE clause of the parent statement. When 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 them together when evaluating access paths and joins. The optimizer can unnest most subqueries, with some exceptions. Those exceptions include hierarchical subqueries and subqueries that contain a
ROWNUM pseudocolumn, one of the set operators, a nested aggregate function, or 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:
EXISTScorrelated subqueries, as long as they do not contain aggregate functions or a
You can unnest an uncorrelated
INsubquery by specifying the
MERGE_AJhint in the subquery.
You can unnest other subqueries by specifying the
UNNESThint in the subquery.
"Hints" for information on hints