Prev: NOT IN clause vs IN clause
Next: cheap wholesale Tag Heuer watch
From: gazzag on 13 Jul 2010 07:44
On 13 July, 05:38, "dn.p...(a)gmail.com" <dn.p...(a)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
From: Mark D Powell on 13 Jul 2010 10:16
On Jul 13, 7:44 am, gazzag <gar...(a)jamms.org> wrote:
> On 13 July, 05:38, "dn.p...(a)gmail.com" <dn.p...(a)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 --


 | 
Pages: 1
Prev: NOT IN clause vs IN clause
Next: cheap wholesale Tag Heuer watch