Ivan Prenosil wrote:

>> Thank you for your replies.
>> The selectivity within the not null part was good, unique constraint so
>> no duplicates. I did change the null values to some negative impossible
>> so that the index will work but I have to drop the unique constraint and
>> to impose unique by trigger.
>
>> I was (wrongly) assuming that the null values will be also indexed (to
>> either start or end of the index).
>
> Null values ARE indexed, it is the "NOT" clause in "where my_field is NOT null"
> that cause you problems.
> If negative values are impossible for this column, try
>
> WHERE my_field >= 0
>
> Ivan
> http://www.volny.cz/iprenosil/interbase/
>

Thank you for your answer.

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).
I think that the NOT is the problem to all indexed queries e.g
WHERE my_field >10 will be indexed and WHERE NOT(my_field >10) is not
indexed. In most of the cases the NOT can be reversed inside the query
but IS NULL cannot except the above mentioned cases.
Is this a standard way with NOT?

TIA
Radu