[펌] Tuning WHERE NOT EXISTS Tips

Tuning WHERE NOT EXISTS Tips Oracle Tips by Burleson Consulting March 3, 2012

Question: I have a query that contains a "where not exists clause" and I want to tune it for faster performance.  What guidelines exists for tuning where not exists clauses?

Answer:  A where not exists clause is used to subtract one set of data from another set.  In some cases a where not exists is executed once, a non-correlated form of not exists:

select book_title 
where 
   not exists 
   (select 
      book_title 
    from 
      sales);

In other cases you can have a correlated NOT EXISTS query, where the inner query references the outer query.

There are several guidelines for re-writing a where not exists into a more efficient form:

• When given the choice between not exists and not in, most DBAs prefer to use the not exists clause. 
  
• When SQL includes a not in clause, a subquery is generally used, while with not exists, a correlated subquery is used.  
  
• In many case a NOT IN will produce the same execution plan as a NOT EXISTS query or a not equal query (!=).
  
• In some case a correlated NOT EXISTS subquery can be re-written with a standard outer join with a NOT NULL test.
• Some NOT EXISTS subqueries can be tuned using the MINUS operator.Using the MINUS to tune a NOT EXISTS

Example of tuning a NOT EXISTS subquery

Here is the tuning of a NOT EXISTS to replace it with a MINUS clause:

select 
   ename
from
   emp
where
   empno NOT IN
   (select
      empno
    from
      bad_credit
    where
      bad_credit_date > sysdate-365
    );

select 
   ename
from
   emp
where
   empno IN
   (select
      empno
    from
      employees
    MINUS
    select
      empno
    from
      bad_credit
    where
      bad_credit_date > sysdate-365
);

---

Here is a NOT EXISTS subquery example that can be tuned with a standard outer join with a NULL test:

select

   ename

from

   emp

where NOT EXISTS

   (select

      null

   from

      dependents

   where

      emp.empno = dependents.empno

   and

      relation=?mother in law?

   );

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

This is a good example of how a not exists subquery can be written with an outer join and a NOT NULL test.  
 
select distinct
   ename
from
   emp,
   dependents
where
   emp.empno = dependents.empno(+)
and
   relation(+)='mother in law'
and
   dependents.empno is null;

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