Nando Dessena wrote:

> Kjell,
> I am not sure what's your point. Do you think I have explained the
> situation accurately or not? If so, and if you agree that Firebird
> could use the suggested improvement, then take it to the devels.

OK, sorry. It's just that I'm a bit frustrated by the always reoccurring
fact that FB seems to require me as an app developer to know a lot more
about the internals of FB than SQL Server requires me to know about SQL
Server's internals to get decent (not optimal, decent) performance with
most queries.

> KR> With that in mind, > or < would seem like a better analogy than <>,
> KR> and in that case, yes, I would expect the index to be used.
>
> There are two cases here:
> - the case with "is not null" doesn't require "> or <", ">" is
> enough.

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

> - 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.

I didn't understand that last bit.

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

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

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

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