BC remote Oracle DBA - Call (800) 766-1884  
Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

 

 

 

 

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.


Expert Remote DBA

BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call
BC Remote DBA today.

 

 

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracleģ is the registered trademark of Oracle Corporation.



Hit Counter