Re: Question on NULLs in index

Hi Bill,

        I suspect the problem (don't have my manuals handy) is that nulls aren't included in the index. The logic of this is traced back to the concept that a null is not a value. An indexed column may contain null values but those values are not included in the index, hence the full table scan.

regards

Jerry

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
>
>

--

Jerry Gitomer         	ICT Group
jgitomer_at_ictgroup.com	Langhorne PA
jgitomer_at_yahoo.com 	Opinions are mine not those of ICT Group