Kjell,

>> There are two cases here:
>> - the case with "is not null" doesn't require "> or <", ">" is
>> enough.

KR> I meant that an analogy with > or an analogy with < would have been better.

forget the analogy. Talking about a possible improvement from now on.

>> - the general case would use that, but I'm not sure that the optimizer
>> can always tell if two index scans are better than a natural scan.

KR> I didn't understand that last bit.

for "general case" I mean expressions like "somefield <> somevalue"
(not related to is null/is not null).
AFAIK, currently Fb never uses an index for "<>". Could it? Yes,
provided the expression is internally rewritten as "somefield <
somevalue or somefield > somevalue". Would that be more efficient? I
don't know. I seem to recall Dmitry Y. talking about this kind of
optimizations in the past, and saying they would be taken into
consideration in some future version of Fb.

The "is null" case is simpler, since nulls are indicated by a
well-known value in the index keys (again, that's AFAIK). Assuming
that values sorts lower than any other possible value, then Firebird
could rewrite "somefield is not null" as "somefield > X", where X is
that well-known value. In that case, an index on somefield could be
used.

KR> I would expect "is null" to go to the index and "return" those records,
KR> at one end of the index, that have null in my column. This might be
KR> described with the "<" analogy.

KR> I would expect "is not null" to go to the same index and skip those
KR> records, at one end of the index, that have null in my column and
KR> "return" the rest of the records. This might be described with the ">"
KR> analogy.

KR> I still don't understand why FB seems to do the former but not the
KR> latter, and that's what I'd like to know.

I think nulls might be placed at the beginning of an index regardless
of the index direction.

Ciao
--
Nando Dessena
http://www.flamerobin.org