Re: Performance on queries with a lot of AND / OR operators

Hi William,

Thank you, your answer is very helpful.

There are 1000 concurrent users (worst case), but they won't be fireing those queries all the time.
It is just when they do an Advanced Find. My question is on this level, actually:

You say that a query

(1) SELECT x FROM y WHERE z IN (a,b);

if different from a query

(2) SELECT x FROM y WHERE z IN (a,b,c);

with respect to parsing and caching. So Oracle needs to parse & execute it completely. Which takes time.

But, if we go from a point where Oracle performs a query like #2 compared to:

(3) SELECT x FROM y WHERE ((z IN (a,b,c))

                      AND  (x IN (d,e,f)));

(pls don't mind the semantics) These (#2 versus #3) are also queries,
which need to be parsed & executed again.

My question is: although it will take more time to execute #3 compared to #2, roughly how much extra time will this take, compared to executing #1 versus #2? Is that a 100% extra, or 10% extra.

Would you have some insight in that?

Thanks & Cheers,
Aritha. Received on Tue Aug 23 2005 - 02:04:11 CDT