Subject Re: not null in statement very slow
Author Svein Erling
--- In, "sascha_luttmann" wrote:
> Hello,
> I have the following problem:
> in my database there are two tables (MOV_ORDERS and
> I fetch records from both tables and check in my query if the
> date is not null. Something like this:
> select …. from … where (ID=:ID) and (DELDATE is not null)
> The problem is that this check for "not null" needs a very long time
> to get the results. The query is much faster when try this:
> select ... from … where (ID=:ID) and ((DELDATE >= '31.12.1899') and
> (DELDATE <= '31.12.2004'))
> Both statements return the same result but the first one needs ten
> times as long as the second one. How come?

Because your index on ID is pretty selective whereas your index on
DELDATE isn't. I guess the fastest select you could do to achieve your
result would be

select ... from ... where (ID=:ID) and (DELDATE is not null or 2=0)