Search code examples
oracle-databaseoptimizationnull

Oracle 10g - optimize WHERE IS NOT NULL

We have Oracle 10g and we need to query a 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

Solution

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