Wednesday, August 8, 2007

How to use an index on a nullable field - in Oracle database

If You have a table T and there is a field x which is nullable then an index on that column (b*tree) index would not be used by oracle in a query like below

select * from t where x = :bv1.

but there is a workaround for this

you can create an index like this

create index t_b1 on t(0,x) ;

then oracle will use that index.