54 lines
2.0 KiB
Plaintext
54 lines
2.0 KiB
Plaintext
== Why is this an issue?
|
|
|
|
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
|
|
|
|
[source,text]
|
|
----
|
|
BEGIN
|
|
SELECT col1
|
|
BULK COLLECT INTO result
|
|
FROM table1
|
|
WHERE col2 IN (SELECT col3 FROM table2); -- Noncompliant
|
|
END;
|
|
/
|
|
----
|
|
|
|
|
|
=== Compliant solution
|
|
|
|
[source,text]
|
|
----
|
|
BEGIN
|
|
SELECT col1 -- Compliant
|
|
BULK COLLECT INTO result
|
|
FROM table1
|
|
JOIN table2 ON col2 = col3;
|
|
END;
|
|
/
|
|
----
|
|
|