--- In firebird-support@yahoogroups.com, Radu Sky <skysword76@...> wrote:

>
> Hello,
>
> I have a table with a field that has about 20% not null values and 80%
> null and I want to select the not null values with a help of an index.
> However, if I
>
> SELECT my_field FROM my_table WHERE my_field IS NOT NULL
>
> the optimizer use natural, no matter that I set the index ascending or
> descending. I think that it should normally use it, given that there

are

> far less values for not null.
>
> SELECT my_field FROM my_table WHERE my_field IS NULL
> use that index.
>
> Is this a normal behavior?

Have you explicitly set the plan to prove to yourself which method is
fastest? Are there a lot of duplicates? If so, the selectivity will be
so bad that it may be cheaper to just read the whole table then to use
the index. Remember, there is a cost in reading the index, so it is
only valuable when it reduces the pages read by at least the number of
pages of index it had to read.

Of course you could also force it to use the index by using

SELECT my_field FROM my_table WHERE my_field < 0 or my_field >= 0

(NULL will fail that condition)


Adam