index with null value field

[BGiulio]

Hello folks,

a simple question: I have a 'select' statement with such a 'where' condition

select from ... where X is null

I know that it is useless to put an index on 'X' column because every NULL is different from another NULL for Oracle.
Is it true? There is no way to increase the performance of such a query?

(I hope I'm wrong....)

Thanks

[Steve Lewis]

Just because NULL means 'undefined value' or 'unknown' does not mean that NULL values are not placed in indexes. When you query by a known value it returns just the rows with that value.

I just did a test on a big table where an indexed column had 369000 NULLs and 4 rows where the column = "000000". Querying the count of "000000"s returned 4 (it ignored the NULLS) and counting the NULLS returned 369000 straight away.

Before judging whether to add an index to a column you need to judge the following:

Is the table write-bound? Ratio of writes/reads?
How many rows in the table? These days with expensive optimisers the cutover point can get up to 1000 rows before its worth adding an index. This depends on the record length and number of records to a page.
How many updates to this column generally?
What is the cardinality of the column i.e. the number of unique values? The more the better. If its a flag then generally don't bother with the index, consider partitioning instead.
How many applications/queries access this column? It may be better to leave it un-indexed if it is hardly ever queried-by. Are you just looking into this for data-checking or everyday use?