Oracle 10g - optimize WHERE IS NOT NULL

Question

We have Oracle 10g and we need to query 1 table (no joins) and filter out rows where 1 of the columns is null. When we do this - WHERE OurColumn IS NOT NULL - we get a full table scan on a very large table - BAD BAD BAD. The column has an index on it but it gets ignored in this instance. Are there any solutions to this?

Thanks

Answer 1

The optimizer thinks that the full table scan will be better.

If there are just a few NULL rows, the optimizer is right.

If you are absolutely sure that the index access will be faster (that is, you have more than 75% rows with col1 IS NULL), then hint your query:

SELECT  /*+ INDEX (t index_name_on_col1) */
        *
FROM    mytable t
WHERE   col1 IS NOT NULL

Why 75%?

Because using INDEX SCAN to retrieve values not covered by the index implies a hidden join on ROWID, which costs about 4 times as much as table scan.

If the index range includes more than 25% of rows, the table scan is usually faster.

As mentioned by Tony Andrews, clustering factor is more accurate method to measure this value, but 25% is still a good rule of thumb.

Answer 2

Create an index on that column.

To make sure the index is used, it should be on the index and other columns in the where.

ocdecio answered:

If you are doing a select *, then it would make sense to do a table scan rather than using the index.

That's not strictly true; an index will be used if there is an index that fits your where clause, and the query optimizer decides using that index would be faster than doing a table scan. If there is no index, or no suitable index, only then must a table scan be done.

Answer 3

Using hints should be done only as a work around rather than a solution.

As mentioned in other answers, the null value is not available in B-TREE indexes.

Since you know that you have mostly null values in this column, would you be able to replace the null value by a range for instance.

That really depends on your column and the nature of your data but typically, if your column is a date type for instance:

where mydatecolumn is not null Can be translated in a rule saying: I want all rows which have a date.

Then you can most definitely do this: where mydatecolumn <=sysdate (in oracle)

This will return all rows with a date and ommit null values while taking advantage of the index on that column without using any hints.