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
|
|
Get the Complete
Oracle SQL Tuning Information
The landmark book
"Advanced Oracle
SQL Tuning The Definitive Reference" is
filled with valuable information on Oracle SQL Tuning.
This book includes scripts and tools to hypercharge Oracle 11g
performance and you can
buy it
for 30% off directly from the publisher.
|