Re: Index on nullable column

"Jack Wang" <nospam_at_nospam.com> wrote in message news:5trfb.44756$H86.1115040_at_news1.telusplanet.net...
> As per http://www.ixora.com.au/tips/not_null.htm,
>
> "An index on a nullable column cannot be used to drive access to a table
> unless the query contains one or more predicates against that column that
> exclude null values"
>
> Though based on my test, index on a nullable column is used to access a
> table. Can someone clarify?
>
> DB: 9.2.0.3
> OS: W2K AS
>
> Thanks.
>
> - Jack
>
>

It means that the query will not use a index on a nullable column if the query itself require looking up null values.

Consider a table like this t1 (a number, b varchar2(40)) and suppose there is a nullable index on column "b".

Then query like this will not use the nullable index in any case (since null values are not stored in the index): select * from t1 where b is null;

..
That is all what the note is trying to say.

Also note: The above note applies to B*Tree indexes only. Bitmap indexes can still use the index on a nullable column.

Anurag Received on Sat Oct 04 2003 - 12:43:14 CDT