 |
|
Tuning Subqueries with NOT IN and NOT EXISTS
Clauses
Oracle Tips by Burleson Consulting |
Now, let’s look at using Oracle SQL for
queries with the NOT IN and NOT EXISTS conditions. As you know from
Chapter 12, a subquery that has the NOT IN or NOT EXISTS clause is
called an anti-join. It is called an anti-join because the purpose
of the subquery is to eliminate rows from the outer table result
set.
Noncorrelated Subqueries using the NOT IN Operator
There are cases where we need to use a
subquery with the NOT IN clause. To illustrate, consider the
following query to show employees who do not have a bad credit
rating in the past year. I also noted in Chapter 12 that a
noncorrelated query with the NOT IN clause can be dangerous. Prior
to Oracle 7.3, Oracle would perform a full-table scan of the
bad_credit table for each row in the outer query. So if there
were 10,000 employees, Oracle would have to perform 10,000
full-table scans of the bad_credit table.
Select /*+
rule */
ename
from
emp
where
empno NOT IN
(select
empno
from
bad_credit
where
bad_credit_date > sysdate-365
)
;
Starting with Oracle 7.3, Oracle will
sometimes transform a noncorrelated subquery with a NOT IN into a
standard join. In Oracle8, the default is to automatically rewrite
anti-joins, and the initialization parameter always_anti_join
defaults to nested_loops. However, you can also explicitly
set always_anti_join=hash (and sometime merge). Here
we see the default execution plan in Oracle8i, without
setting the always_anti_join parameter.
OPERATION
------------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
1
FILTER
1
TABLE ACCESS
FULL
EMP
1
TABLE ACCESS
FULL
BAD_CREDIT
2
The anti-join is the opposite of an equi-join.
For example, in the preceding anti-join, Oracle takes an emp
row and then checks for a matching row in the bad_credit
table. If a row is found, then that row is eliminated from the
result set and the next emp row is retrieved. In sum, instead
of checking if the outer rows exists in the inner query, the
anti-join verifies that the rows do not exist in the inner query.
Another common way to rewrite noncorrelated
anti-join subqueries is to utilize the Oracle SQL minus
clause. To illustrate, here we have changed the NOT IN subquery into
an IN subquery:
select /*+ rule */
ename
from
emp
where
empno IN
(select
empno
from
employees
MINUS
select
empno
from
bad_credit
where
bad_credit_date > sysdate-365
)
;
Here is the execution plan for this query.
Here we see that Oracle must execute the query against the emp
table twice, but this is still faster than executing the subquery
once for each row in the outer table.
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
1385
NESTED LOOPS
1
VIEW
VW_NSO_1
1
MINUS
1
SORT
UNIQUE
1
TABLE ACCESS
FULL
EMP
1
SORT
UNIQUE
2
TABLE ACCESS
FULL
BAD_CREDIT
1
TABLE ACCESS
BY INDEX ROWID
EMP
2
INDEX
RANGE SCAN
EMP_EMPNO
1
Next let’s take a look at subqueries that use
the NOT IN operator.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
