Table Anti-Join Hints Oracle Tips by Burleson Consulting

Now that we have covered table joins, let’s explore the SQL anti-join. An anti-join is an operation that is generally used when the SQL statement specifies a NOT IN or a NOT EXISTS clause. For example, the following query is used to locate customers who do not have bad credit.

select
   customer_name
from
   customer
where
   customer_number NOT IN
   (
    select
       customer_number
    from
       bad_credit_history
   )
;

As you can see, there are many legitimate times when you will need to filter rows from one table in terms of the nonexistence of rows in another table. Let’s take a look at some useful Oracle hints that can aid you in your quest to make anti-joins efficient.

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_aj Hint

The merge_aj hint is placed in a NOT IN subquery to perform an anti-join where full-table access is preferred over index access. As an example, consider this query, where we display the names of all departments that have no salesmen:

select
   dname
from
   dept
where
   deptno NOT IN
   (select
      deptno
    from
      emp
    where
      job = 'SALESMAN')
;

The performance of this type of query can be extremely poor when null values are allowed for the data column in the subquery. The subquery is reexecuted once for every row in the outer query block! Here is the execution plan:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     1
  FILTER
                                                                     1
    TABLE ACCESS
FULL                           DEPT                                  1
    TABLE ACCESS
BY INDEX ROWID                 EMP                                   2
      INDEX
RANGE SCAN                     JOB_IDX                               1

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; it 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 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.

Now, we add a merge_aj hint to the subquery, and also ensure that there is a NOT NULL constraint on the deptno column.

select
   dname
from
   dept
where
   deptno NOT IN
   (select /*+ merge_aj */
      deptno
    from
      emp
    where
      job = 'SALESMAN')
;

Here we see that the execution plan for the query has changed and the merge anti-join is invoked in place of the filter operation:

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     5
  MERGE JOIN
ANTI                                                                 1
    SORT
JOIN                                                                 1
      TABLE ACCESS
FULL                           DEPT                                  1
    SORT
UNIQUE                                                               2
      VIEW
                               VW_NSO_1                              1
        TABLE ACCESS
BY INDEX ROWID                 EMP                                   1
          INDEX
RANGE SCAN                     JOB_IDX                               1

The hash_aj Hint

The hash_aj hint is placed in a not in subquery to perform a hash anti-join in cases where a hash join is desired. Here is an example of the hash_aj hint placed inside a subquery:

select
   dname
from
   dept
where
   deptno NOT IN
   (select /*+ hash_aj */
      deptno
    from
      emp
    where
      job = 'SALESMAN')
;

Here we see that the execution plan specifies a hash join, with a full-table scan on the department table.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
 SELECT STATEMENT
                                                                     3
  HASH JOIN
ANTI                                                                 1
    TABLE ACCESS
FULL                           DEPT                                  1
    VIEW
                               VW_NSO_1                              2
      TABLE ACCESS
BY INDEX ROWID                 EMP                                   1
        INDEX
RANGE SCAN                     JOB_IDX                               1

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.