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?

On 13 July, 05:38, "dn.p... (AT) gmail (DOT) com" <dn.p... (AT) gmail (DOT) com> wrote:
Generate an explain plan for each query.

HTH
-g

On Jul 13, 7:44*am, gazzag <gar... (AT) jamms (DOT) org> wrote:
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 --

On Jul 12, 9:38*pm, "dn.p... (AT) gmail (DOT) com" <dn.p... (AT) gmail (DOT) com> wrote:
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/2...rformance.html

jg
--
@home.com is bogus.
http://www.computerworld.com/s/artic...ect_mill ions