 |
|
Correlated Subqueries Using the EXISTS Clause
Oracle Tips by Burleson Consulting |
Oracle calls this class of subqueries
correlated because a Boolean condition in the where clause of
the inner query references a corresponding row in the outer query.
The restrictions that must be met before Oracle can transform the
correlated subquery to a join include these:
-
The correlated subquery must use the EXISTS
clause.
-
The outer query cannot also be a subquery
(For example, a nested subquery).
-
The correlation criteria in the inner query
must use the equality operator, “=”.
-
The subquery cannot contain a group by
or connect by reference.
-
The equality operator in the subquery must
only return a single row.
These restrictions greatly limit the number
of automatic transformations by the SQL optimizer. Especially
limiting is the requirement that the query use the EXISTS clause,
such that this transformation will not occur when using the IN
clause.
However, just because Oracle does not
transform the subquery does not mean that you cannot manually
transform your correlated subquery. This type of transformation
generally involves moving the subquery to the from clause of
the surrounding query, thereby changing the subquery into an in-line
view. Additionally, some types of correlated subqueries can be
directly merged into the surrounding subquery rather than moved to
the from clause.
The rule for automatic transforming a
correlated subquery is simple. The only rule is that only one row is
returned from the subquery for the corresponding correlation
criteria.
For example, the following query returns all
employees with bad credit:
select
ename
from
emp e
where EXISTS
(select
null
from
bad_credit b
where
e.empno=b.empno
)
;
Here we see the execution plan for this query. Note
that the subquery has been removed and the expected VIEW access
method is replaced by a full-table scan.
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
1
FILTER
1
TABLE ACCESS
FULL
EMP
1
INDEX
RANGE SCAN
BAD_EMPNO
2
However, just as in the case of
non-correlated subqueries using the IN clause, we are better off
rewriting the subquery. This is because we are assured that the
query is always transformed, and we can also add hints to improve
the join method. Here we see the equivalent query, rewritten as a
standard join to use the rule hint.
select /*+
rule */
ename
from
emp e,
bad_credit b
where
e.empno=b.empno
;
Next we see that the execution plan is
different from the automatic query transformation.
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
NESTED LOOPS
1
TABLE ACCESS
FULL
BAD_CREDIT
1
TABLE ACCESS
BY INDEX ROWID
EMP
2
INDEX
RANGE SCAN
EMP_EMPNO
1
We also see that the standard join is easier to
understand and also allows us to alter the execution plan by adding
hints.
TIP: Oracle will automatically transform
correlated subqueries that use the EXISTS clause. However, you
should always manually rewrite this form of subquery as a standard
join so that you can take advantage of hints.
Next, let’s examine anti-joins, which you
know are non-correlated subqueries with the NOT IN operator.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
