Query Optimization - Not In

Query Optimization - Not In

NOT IN clause in Oracle is very expensive and let the optimizer FULL 
Scan your tables. Because NOT IN does not use a limiting condition.

A way to overcome this problem is to rewrite the query with OUTER JOIN.
Outer Joins adds a limiting condition to your queries that can reduce 
the number of full table scans necessary
 

Example of NOT IN
-----------------------------
SELECT * FROM MYEMP 
WHERE DEPTNO NOT IN 
(
 SELECT DEPTNO FROM MYDEPT
);


Above query with Outer Join
-----------------------------
SELECT MYEMP.* FROM MYEMP, MYDEPT 
WHERE MYEMP.DEPTNO = MYDEPT.DEPTNO(+) 
AND MYDEPT.DEPTNO IS NULL;



Get More Query Optimization Tips:
http://nimishgarg.blogspot.in/2012/10/query-optimization-tips-for-oracle.html