Re: NOT IN clause vs IN clause

From: joel garry <joel-garry_at_home.com>
Date: Fri, 16 Jul 2010 22:10:40 -0700 (PDT)
Message-ID: <ecfb5c4f-5904-4eb1-ac7a-991ff26bf2f8_at_p11g2000prf.googlegroups.com>

On Jul 12, 9:38 pm, "dn.p..._at_gmail.com" <dn.p..._at_gmail.com> wrote:
> On Oracle 10 hosted on a FreeBSD server, I am seeing a
> disproportionate difference in response time between two queries which
> should take same amount of time.
>
> my_folder is a table with only 22,000 entries.
> Query 1 :   (takes 22-23 seconds)
> select f.foldername from my_folder f
>     where f.foldername in (select f1.foldername from my_folder f1,
> my_aa a, my_bb b
>                                         where some-conditions)
>     and f.foldername.isactive = 1 ;
>
> Query 2 :  (I aborted it after 15 minutes)
> select f.foldername from my_folder f
>     where f.foldername   not  in  (select f1.foldername from my_folder
> f1, my_aa a, my_bb b
>                                         where some-conditions)
>     and f.foldername.isactive = 1 ;
> The only difference is that query 2 uses 'NOT IN' clause instead of
> 'IN' clause.
>
> Why should this happen?

In addition to what the others noted, think of this:

How long would it take to count all the people in London? How long would it take to count all the people not in London? What about all the people who've died while you are counting?

Of course there are more ways to evaluate your query that the explain plan will hopefully make more clear. Search for NOT IN on asktom.oracle.com for some clarification. There are also other considerations for performance tuning. See http://oracle-randolf.blogspot.com/2009/02/basic-sql-statement-performance.html

jg

--
_at_home.com is bogus.
http://www.computerworld.com/s/article/9179224/Researchers_Authentication_crack_could_affect_millions

Received on Sat Jul 17 2010 - 00:10:40 CDT