NOT IN clause vs IN clause

From: <dn.perl_at_gmail.com>
Date: Mon, 12 Jul 2010 21:38:10 -0700 (PDT)
Message-ID: <4d2f84db-2553-4799-a46f-5cd928797183_at_a14g2000pro.googlegroups.com>

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? Received on Mon Jul 12 2010 - 23:38:10 CDT