Re: is Null or = null and a slow query

"Jack Addington" <jaddington_at_shaw.ca> wrote:

>
><willy_gates_at_hotmail.com> wrote in message
>news:1131120551.077847.20910_at_g43g2000cwa.googlegroups.com...
>> Yes this seems to speed things up a bit.
>>
>> Follow up questions:
>>
>> 1> Why?
>> 2> What about if my columns could contain null values?
>>
>
>1) I am working on a similar query and I believe the reason is that the
>combination of the null = and the bind variables confuses the optimiser. I
>had finally tweaked this monster query to have excellent response and then I
>threw it in a PL/SQL block and the performance crashed hard. When I run in
>sql window without bind variables then the optimizer ignores all the where
>clause entries where I have null = null or ... I am playing around with the
>nvl bit as well.
>
>2) I'm not sure what the best method is but I am going to try adding some
>function based indexes to remove the null from equation.
>
>

Never, never use
null =
or
null <>

Null cannot be compared to anything, even itself... Only IS and IS NOT are used when referencing NULL....

where <somefield> IS NULL or where <somefield> IS NOT NULL Received on Fri Nov 04 2005 - 12:40:18 CST