Re: NOT IN clause vs IN clause

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 13 Jul 2010 07:16:01 -0700 (PDT)
Message-ID: <d2120437-70ec-41d5-a328-dd81f5fe03f5_at_j8g2000yqd.googlegroups.com>

On Jul 13, 7:44 am, gazzag <gar..._at_jamms.org> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

The suggested use of explain plan is definitely the way to start.

Also be aware that a NOT IN is not just the opposite of an IN clause. Place a NULL in the list and test the results of the query verse the same query with no NULL values in the list (or being returned by the in-list sub-query).

HTH -- Mark D Powell -- Received on Tue Jul 13 2010 - 09:16:01 CDT