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

If I understand correctly, you are talking about a code generator that will prepare and send sql statements to a database. Some of the absolute worst, most unoptimizable code I have ever seen has come from code generators. In order to stand a chance at optimizing the code, the Cost-Based Optimizer (CBO) has to be given something decent to start with. Then it does a pretty good job (usually, but not always).

To answer your questions directly, yes, it is likely to have a very bad impact on peformance.

To move forward, you need to
1) Learn everything you can about how Oracle processes sql, including how the CBO works (and what it cannot do). 2) Structure your data to support these types of queries. It probably means a lot of denormalization, but that may be the only way to help.

Regards,
Dan Fink Received on Thu Aug 18 2005 - 11:51:31 CDT