Oracle 10g - optimize WHERE IS NOT NULL

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

The optimiser will make its decision based on the relative cost of the full table scan and using the index. This mainly comes down to how many blocks will have to be read to satisfy the query. The 25%/75% rule of thumb mentioned in another answer is simplistic: in some cases a full table scan will make sense even to get 1% of the rows - i.e. if those rows happen to be spread around many blocks.

For example, consider this table:

SQL> create table t1 as select object_id, object_name from all_objects;Table created.SQL> alter table t1 modify object_id null;Table altered.SQL> update t1 set object_id = null  2  where mod(object_id,100) != 0  3  /84558 rows updated.SQL> analyze table t1 compute statistics;Table analyzed.SQL> select count(*) from t1 where object_id is not null;  COUNT(*)----------       861    

As you can see, only approximately 1% of the rows in T1 have a non-null object_id. But due to the way I built the table, these 861 rows will be spread more or less evenly around the table. Therefore, the query:

select * from t1 where object_id is not null;

is likely to visit almost every block in T1 to get data, even if the optimiser used the index. It makes sense then to dispense with the index and go for a full table scan!

A key statistic to help identify this situation is the index clustering factor:

SQL> select clustering_factor from user_indexes where index_name='T1_IDX';CLUSTERING_FACTOR-----------------              460

This value 460 is quite high (compared to the 861 rows in the index), and suggests that a full table scan will be used. See this DBAZine article on clustering factors.

If you are doing a select *, then it would make sense to do a table scan rather than using the index. If you know which columns you are interested in, you could create a covered index with those colums plus the one you are applying the IS NOT NULL condition.