Tuesday, July 23, 2013

Oracle Tuning :Comparison of "Not in" and "minus" in Oracle select query

We have two queries :-

select * from t1
where a.t1 not in (select b from t2)

and other is,

select a from t1
minus
select b from t2


the second query will run very fast while the first one will take longer time.With MINUS, a full scan is done on both tables and the results for t2 are removed from the results for t1.

With NOT IN, a full table scan is done on t1. For each t1 row, a lookup is then done in t2. If no row is found in t2, the t1 row is returned .

No comments:

Post a Comment