Re: NOT IN clause vs IN clause

From: gazzag <gareth_at_jamms.org>
Date: Tue, 13 Jul 2010 04:44:23 -0700 (PDT)
Message-ID: <c6bb91ed-1f11-41a5-8f0d-fafc03a3321c_at_u26g2000yqu.googlegroups.com>

On 13 July, 05:38, "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?

Generate an explain plan for each query.

HTH
-g Received on Tue Jul 13 2010 - 06:44:23 CDT