At 07:03 AM 15/02/2006, you 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?

Yes. You say "values for not null" but there is no such thing as a
"value for not null". NOT conditions never use an index, since an
index can never return a "value of NOT-something".

If you think your query ** would be made faster by using an index on
this column, then try to rephrase your predicate so that it asks a
POSITIVE question whose answer depends on the *value* of the non-null
items, e.g. "BETWEEN <bottom-of-range> and <top-of-range>", or ">=
<minimum-value>". This way, with a normal ASC index, it will use the
index and won't even consider rows with NULL.

** Don't be surprised if forcing it to use an index on a column with
this sort of distribution actually makes it slower than the table
walk. The geometry of this index might not be particularly
wonderful, and it will keep getting worse as the table grows if it
continues to have the same kind of distribution in real life.

./heLen