 |
|
The Anti-Join
Oracle Tips by Burleson Consulting |
An
anti-join operation is a case where we have a non-correlated
subquery with a NOT IN or NOT EXISTS clause.
Essentially, an anti-join is a subquery where any rows found in the
subquery are not included in the result set. An
anti-join returns rows
from the left side of the predicate for which there is no
corresponding row on the right side of the predicate. That is, it
returns rows that fail to match the subquery on the right side
(Figure 16-4).
Figure 4: An
anti-join
For example, an anti-join can select a list
of employees who have not received a bonus.
select /*+
first_rows */
ename,
deptno
from
emp
where
ename NOT IN
( select
ename
from
bonus
)
;
Here is the execution plan for this query. Note that
the Oracle CBO recognizes that there is no where clause for
the subquery and properly invokes a full-table scan because all of
the rows in both tables are required to satisfy the query.
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
FILTER
1
TABLE ACCESS
FULL
EMP
1
TABLE ACCESS
FULL
BONUS
2
Wow, two full-table scans. This would be a
very time-consuming query if these were large tables. I mentioned in
earlier chapters how the NOT IN clause can sometime be
replaced by a standard join. In the case of this query, we are
interested in a display of those employees who have not received a
bonus. We could use a standard join into the bonus table and then
eliminate matching rows in the bonus table (i.e., where comm
is not null). Let’s try it:
select /*+
first_rows */
emp.ename,
emp.deptno
from
emp,
bonus
where
emp.ename = bonus.ename(+)
and
bonus.comm is null;
Here we do a standard outer join, so that we
can use an index, and we then remove unwanted rows for the employees
who have received a bonus.
OPERATION
----------------------------------------------------------------------
OPTIONS
OBJECT_NAME
POSITION
------------------------------ ----------------------------
----------
SELECT STATEMENT
FILTER
1
NESTED LOOPS
OUTER
1
TABLE ACCESS
FULL
EMP 1
TABLE ACCESS
BY INDEX ROWID
BONUS
2
INDEX
RANGE SCAN
BONUS_ENAME
1
Here we have greatly improved the overall
speed of the query by replacing the NOT IN clause. The optimizer
uses a nested loops algorithm for NOT IN subqueries by default,
unless the always_anti_join initialization parameter is set
to merge or hash, provided that all of the required
conditions for merge and hash joins are met as explained in Chapter
12.
Table Anti-Join Hints
Let’s take a look at some useful Oracle hints
that can aid you in your quest to make anti-joins efficient. For
details, see Chapter 12.
However, it is still a good idea to
discourage general use of the NOT IN clause (which invokes a
subquery) and to prefer NOT EXISTS (which invokes a correlated
subquery), since the query returns no rows if any rows
returned by the subquery contain null values.
The Merge Anti-Join
The merge anti-join is performed in a
NOT IN subquery to perform an anti-join where full-table access is
preferred over index access.
There is an alternative method for evaluating
NOT IN subqueries that does not reevaluate the subquery once for
each row in the outer query block and should be considered when the
outer query block generates a large number of rows. This method can
only be used when NOT NULL predicates exist on the subquery column
and you have a hint in the subquery query block. The anti-join can
be executed as either a hash_aj or a merge_aj hint
depending on the desired join type.
WARNING: The anti-join hints merge_aj
and hash_aj will only work if the column requested in the
NOT IN clause has a NOT NULL constraint.
In sum, the merge_aj and hash_aj
hints may dramatically improve the performance of NOT IN
subqueries, provided that the subquery column is NOT NULL. Next,
let’s take a look at how you can direct the Oracle optimizer to use
a specific index.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.
