Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Finding null values in a large table
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
create index the_nulls on the_table (
decode(col,null,-1,null))
select ..
from the_table
where decode(col,null,-1,null) = -1
or similar. Obviously the idea is that you don't have lots and lots of nulls - otherwise the full scan will be better anyway
hth
connor
-- ============================== Connor McDonald http://www.oracledba.co.uk "Some days you're the pigeon, some days you're the statue..."Received on Sat Dec 07 2002 - 06:27:44 CST