Re: Question on NULLs in index

My guess is that you have a lot of nulls in that column. Usually the optimizer will opt for a full scan if the number of rows returned is estimated to be x percent of the table anyway.

In article <35AA5E31.33AE_at_ictgroup.com>,   jgitomer_at_ictgroup.com wrote:
> 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
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum Received on Mon Jul 13 1998 - 18:57:47 CDT