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

 

 


 

 

 

 

 

 

 

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.


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