Richard Wesley wrote:

> On Feb 15, 2006, at 05:23, Radu Sky wrote:
>
>> WHERE my_field >= 0 combined with WHERE my_field IS NULL solved my
>> problem, however (to be picky) this solution will not work unless
>> it is
>> known an imposed min/max value for <my_field> (which mostly is).
>
> Then how about "where field >= 0 or field <= 0"?
> ________________________________________________________

This "where field >= 0 or field <= 0" should do fine, at least in theory.
I checked the performance analysis with IBExpert and I saw a strange thing.
The test table had 56 records with 5 nulls, the minimum value was 1.

WHEN my_field IS NULL returned 5 indexed reads

WHEN my_field >0 returned 56 indexed reads (all records). Shouldn't be
51 here?

WHEN my_field >4 returned 52 indexed reads and so on.

It appears that the index parses also the null values which in this case
the index is useful only in IS NULL context, the natural will be
faster when checking for not null values.

Are these wrong or is it me?

TIA
Radu