Tuning de Banco, Tuning de SQL, Ferramentas de tuning

Poston Thu, 02 Aug 2012 2:58 pm

Good afternoon, I wonder what the difference of these operators ... are equal? return the same result?If possible I would like to a instance, already read some texts on the internet ... but I couldn't understand very well their differences. Thank you.

Poston Fri, 03 Aug 2012 9:55 am

In General, they return the same result. One is not necessarily better than the other in terms of performance, will depend on how is your data, and your query.
However, the " NOT IN " and " NOT EXISTS " can return different data when used fields that can be null.
Taking a ready example of tone:
Code: Select all
SQL> select count(*) from emp where empno not in ( select mgr from emp );

Apparently, all is empregrado Manager. However:
Code: Select all
SQL> select count(*) from emp T1
  2  where not exists ( select null from emp T2 where t2.mgr = t1.empno );

with the " not exists " we notice that there are 9 people who are not managers. In " NOT IN " he ignores the NULL.

Another thing, the return within a field EXISTS doesn't matter. It only checks whether the query " " wheel or not.
Location: Fortaleza - CE


Daniel N.N.

Poston Mon, 13 Aug 2012 4:05 pm

Staff, according to the " Oracle Database Performance Tuning Guide ", in certain circumstances it is best to use IN what EXISTS and vice versa.

In General, OLTP environments, if the predicate is selective in subquery use in. If the predicate is selective in query father, use EXISTS.

More details to explain and demonstrate in my workouts of SQL Tuning: ... racle.html [] s Fabio Prado
Location: São Paulo - SP

Fábio Prado

Return to DBA Tuning

Who is online

Users browsing this forum: No registered users and 1 guest