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

aritha_at_zonnet.nl wrote:
> Hi all,
>
> I have a question. Browsed the Internet but was unsuccessfull in
> finding the answer. So hopefully you can help?
>
> Given a UI connecting to an Oracle database. Currently, this UI
> provides users the ability do searches on the database using only an
> AND operator.
>
> This UI needs to be extended to allow OR operators and negators (NOT).
> The UI does not allow the user to select fields which causes extra
> joins. (I read that can be a negative performance impact)
>
> Now, my question is, in an environment where over 1000 clients connect
> to this database, is it likely that this will have a very bad influence
> on performance? Is there any documentation which I can read on this?
> I looked over the Internet but was not able to find anything if the
> query in the field of numer of OR and AND would grow. Only if there are
> more joins etc.
>
> Any suggestion very very welcome ;)
>
> Thanks & cheers,
> Aritha.

It depends. Bear in mind that Oracle caches its SQL, and that flooding it with generated queries is a sure way to defeat that caching system.

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

is a different query, requiring separate parsing and caching, from

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

Now perhaps a dozen or so variations isn't likely to be an issue in the scheme of things, but if your 1000 concurrent users are going to be generating new queries constantly then your system could have serious problems.

Does the system use bind variables? Received on Thu Aug 18 2005 - 17:43:15 CDT