Nando Dessena wrote:

> I don't see anything particularly strange in your case. Equality tests
> use indexes, inequality tests don't. Why do you expect is null and is
> not null to behave differently?
>
> select * from atable where somefield <> 100 doesn't use an index on
> somefield. Does this surprise you?
>
> Perhaps Firebird, internally, could turn "is not null" into the
> equivalent of "> null" (assuming nulls are at the beginning of the
> index). In this case an index would be used.

Yes, I would expect an index to tell the engine which records are null
and which ones are not (in one or a set of columns). And yes, I would
expect that information to be used when it would be useful. Wouldn't you?

The analogy with = 100 vs. <> 100 doesn't seem unquestionable, because I
would expect the nulls to be sorted at one of the ends of the index.
With that in mind, > or < would seem like a better analogy than <>, and
in that case, yes, I would expect the index to be used.

Kjell
--
--------------------------------------
Kjell Rilbe
Adressmarknaden AM AB
E-post: kjell.rilbe@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64