Re: Finding null values in a large table

tldry_at_charter.net (Tim Dry) wrote:
> damorgan <damorgan_at_exesolutions.com> wrote in message
> news:<3DEFEDF0.72B28A23_at_exesolutions.com>...
> > Tim Dry wrote:
> >
> > > I have a fairly large table (millions of rows) and I would like to be
> > > able to find rows that have a null value in a particular column. I
> > > know that regular indexes don't contain null values and thus an index
> > > on this particular column probably wouldn't help. I believe that
> > > bitmap indexes include nulls, but bitmap indexes are supposedly
> > > inefficient when the column is too selective. Are there other
> > > solutions?
> > >
> > > -Tim
> >
> > Is this a one-time only or an ongoing requirement?
> >
> > Dan Morgan
>
> Unfortunately it is ongoing. Very ongoing.

I think you can find these rows rapidly if you build the index backed with a not null column. I.e. create index adfljd on lkjoi (desired_col, notnullable_col). It would behoove you to choose the smallest not null column on the table, to avoid excessively large storage requirements. You could even make a dummy column (say, dummy char(1) default 'x' not null) if there are no other suitable columns to use.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service              New Rate! $9.95/Month 50GB