Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
 

Correlated Subqueries with the NOT IN Operator
Oracle Tips by Burleson
 

Logically, it is very unusual to use a NOT IN subquery with a correlation in the subquery. The NOT is the anti-join that says to filter out matching rows, while the correlation says to specifically match rows in the subquery.

To show this type of subquery, we have created a table called dependents that contains the following data columns.

SQL> select * from dependents;

     EMPNO DEPENDENT_ RELATION
---------- ---------- ----------
      7902 Sarah      daughter
      7902 Janet      spouse
      7934 Jan        spouse
      7934 William    son
      7934 Bob        son
      7934 Marianne   daughter

Here is a query to select all employees without a son.

select
   ename
from
   emp
where empno NOT IN
   (select
      empno
   from
      dependents
   where
      emp.empno = dependents.empno
   and
      relation=ísoní
   )
;

These opposing goals will still work, but the best solution is to remove the subquery and use an outer join with the select distinct clause.

Correlated Subqueries with the NOT EXISTS Operator

Recall that Oracle doesn't consider transforming correlated subqueries that are interfaced via the NOT EXISTS operator. Here is an example of a non-correlated subquery with the NOT EXISTS operator.

This query could be used to locate employees who do not have a son. This type of anti-join will evaluate the inner query for each row in the outer query and filter out all rows except those where the employee has no inner table rows where relation=ísoní.

select
   ename
from
   emp
where NOT EXISTS
   (select
      null
   from
      dependents
   where
      emp.empno = dependents.empno
   and
      relation=ísoní
   )
;

Here is the execution plan. Note the full-table scan against the emp table because the subquery does not use the empno index on the emp table. This full-table scan could be very time-consuming if the emp table had millions of rows.

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

Now, here it would be tempting to try to rewrite this query as an outer join. In fact, we can reformulate the equivalent query by specifying the outer join conditions in a equi-join, being careful to add the select distinct clause to eliminate any duplicate rows.

select distinct
   ename
from
   emp,
   dependents
where
   emp.empno = dependents.empno(+)
and
   relation(+)='son'
and
   dependents.empno is null
;

Here we see a faster execution plan using an OUTER access method with the HASH JOIN access method. In a case where the driving table (in this case the outer emp table) is small, this query will execute far faster than the original subquery.

OPERATION
----------------------------------------------------------------------
OPTIONS                        OBJECT_NAME                    POSITION
------------------------------ ---------------------------- ----------
SELECT STATEMENT
                                                                     6
  SORT
UNIQUE                                                               1
    FILTER
                                                                     1
      HASH JOIN
OUTER                                                                1
        TABLE ACCESS
FULL                           EMP                                   1
        TABLE ACCESS

FULL                           DEPENDENTS                            2

Next, letís look at non-correlated subqueries that use the NOT EXISTS operator.

Non-correlated subqueries with the NOT EXISTS Operator

It never makes sense to use the NOT EXISTS clause in a non-correlated subquery, because the query cannot be resolved.  To illustrate, consider the query below:

select
   ename
from
   emp
where NOT EXISTS
   (select
      null
   from
      bad_credit
   )
;

Next, letís examine how Oracle SQL handles non-equality conditions.


This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.

††
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

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

Oracleģ is the registered trademark of Oracle Corporation. 

Hit Counter