optimization - 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.