Aiesec Regina wrote:
>
> Hi:
>
> I have an index which is not getting used. Here is the situation.
>
> select date_field_a
> from table_one
> where date_field_a IS NULL
>
> I have an index on table_one(date_field_a)
>
> i.e. create index my_index
> on table_one(date_field_a)
>
> When I execute my select, it does a full table scan. I thought it
> should be using the index. I thought that there was always a 1-1
> row relationship between an index it's associated table, and that
> NULLs in indexes were allowed. If that is right, my select should
> have used the index. However, since it did not, my thinking on this
> may be wrong.
>
> I can work around not using the index. Indexing this date field will
> probably not be efficient anyways.
>
> I would just like to know why the index isn't getting used. Is there
> something about NULLs in non-unique indexes that I am missing?
>
> Thanks for any response.
>
> Bill
>
NULLS apparently are never indexed in Oracle, so if you use IS NULL it
can't use the index. Sybase, I believe, does index NULLS which is why
count(*) in Sybase is very fast as it just scans the index whereas in
Oracle a count(*) has to read the whole table.
Received on Mon Jul 13 1998 - 16:55:45 CDT
