Re: Query Optimizer does not ignore constant condition?

Hi Ed,

Didn't I say I don't want to go into why it exists? <g>

Consider that about five sub-conditions, created at different locations in the program, are OR'd together by the client code. That is,

whereClause = part1.getCondition() + "OR" + part2.getCondition() + "OR" ....

While usually there is always something to fill-in in each of these sub-conditions, in one particular (exception) case the third part does not produce a condition.

Instead of changing the client code to check whether the condition is non-null before adding it to the where clause, the programmer decided to return "(1=0)", assuming that the query optimizer will ignore it. But it doesn't.

Hope this satisfies your inquiring mind.

I have modified the strucure so that this is no longer an issue. But I would like to know why this happens.

ed.prochak_at_magicinterface.com (Ed prochak) wrote in message news:<4b5394b2.0308211319.1b5c7db8_at_posting.google.com>...
> h00_at_hotmail.com (hemal) wrote in message news:<16c83c3f.0308201406.29b9f79d_at_posting.google.com>...
> > I came across a very strange situation at work. There is an order of
> > magnitude difference in execution time for the following two queries
> > (10 v/s ~130 msec):
> >
> > select count(*) from table_name where column_name = 'value' or 1 = 0
> >
> > select count(*) from table_name where column_name = 'value'
> >
> > I do not want to go into the reason why the redundent condition
> > exists, the example is representative of the real query where it
> > serves a purpose.
> >
> > Any suggestions what I can do to improve the situation?
> >
> > Thanks in advance.
>
> I have no explaination for you, just a question:
> what purpose does a redundant condition serve?
>
> Enquiring minds want to know!
>
> ed
Received on Fri Aug 22 2003 - 21:44:45 CEST