Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2004
    Posts
    1

    Unanswered: Diffence between NOT IN and NOT EXISTS in performance

    Can anyone please elaborate the difference between NOT In and NOT EXISTS as far as performance is concerned on the lines of what exactly is done in both the cases.


    Thanks in Advance

  2. #2
    Join Date
    Feb 2004
    Location
    India
    Posts
    135

    Re: Diffence between NOT IN and NOT EXISTS in performance

    Originally posted by vish2980
    Can anyone please elaborate the difference between NOT In and NOT EXISTS as far as performance is concerned on the lines of what exactly is done in both the cases.


    Thanks in Advance
    Hi,

    Plz. find ur answer for your Question.

    http://www.smart-soft.co.uk/Oracle/o...ning-part7.htm

    for details I will tell u soon.

  3. #3
    Join Date
    Feb 2004
    Location
    Mumbai
    Posts
    2
    NOT EXISTS is preferable to NOT IN

    select ...
    from emp e
    where not exists (select 'x' from dept where dept_no = e.dept_no and dept_cat = 'A');

    is faster than

    select ...
    from emp e
    where dept_no not in (select dept_no from dept where dept_cat = 'A');

    which requires an internal sort/merge.

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You can also use an outer join i.e.

    select ...
    from emp e, dept d
    where e.dept_no = d.dept_no(+)
    and d.dept_cat(+) = 'A'
    and d.dept_no is null

    As with all sql statments try it on your prod environment and look at the trace after repeated runs ( to eliminate caching issues ).

    Alan

  5. #5
    Join Date
    Jan 2004
    Location
    Singapore
    Posts
    89

    Re: Diffence between NOT IN and NOT EXISTS in performance

    Originally posted by vish2980
    Can anyone please elaborate the difference between NOT In and NOT EXISTS as far as performance is concerned on the lines of what exactly is done in both the cases.


    Thanks in Advance
    \

    NOT IN checks for each and every the list of values given in the IN clause, whereas NOT EXISTS just check for true or false based on the subquery result. So the latter is more faster
    Thanks and Regards,

    Praveen Pulikunnu

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    there is a time and place for every different type of "anti-join"

    NOT IN works great if you are applying it to a not-null column.

    The only way to see what works best with your specific query is to
    BENCHMARK each option.
    Anti-join options perform differently depending on your data.

    Tom Kite does a great job of explaining this and showing examples
    in his book Effective Oracle by Design
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •