We usually handle something like this by putting a bogus default value into the field and then updating the existing nulls to the same bogus value. For example new records had a default of 1/1/1950 in a date field that was indexed. Before when looking for nulls in this 8M row table, it would take a couple of minutes. Searching for 1/1/1950 took a few seconds at most. Of course you need to make sure that the bogus default value doesn't mess up your application by returning records when it didn't before.

Jerry Whittle
COINS DBA
NCI Information Systems Inc.
jerome.whittle.[Email address protected]
618-622-4145
-----Original Message-----
From: Yves [mailto:oracledba-ezmlmshield-x63907235.[Email address protected]

I have a query that needs to look at the records in a table where fieldx is null. It would be nice if I could index this field because the number of null values are 79K and the number of rows in the table is 12M.

Since less than 1% of the rows would be returned, I think that an index would help.

Is there a work around so that I could sort of index this column? Function-based index maybe?

Merci / Thanks
Yves Leonard,
Chef de Projet / Project Leader,
DDS / SDD,
RH Coats, 13-I
Téléphone / phone : 613-951-3233,
couriel / email : yves.[Email address protected]

Oracle LazyDBA home page