[ACCEPTED]-Oracle 10g - optimize WHERE IS NOT NULL-null

Accepted answer
Score: 27

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

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

If you 11 are absolutely sure that the index access 10 will be faster (that is, you have more than 9 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 8 using INDEX SCAN to retrieve values not covered by 7 the index implies a hidden join on ROWID, which 6 costs about 4 times as much as table scan.

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

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

Score: 16

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

For example, consider 14 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 13 1% of the rows in T1 have a non-null object_id. But 12 due to the way I built the table, these 11 861 rows will be spread more or less evenly 10 around the table. Therefore, the query:

select * from t1 where object_id is not null;

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

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

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

CLUSTERING_FACTOR
-----------------
              460

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

Score: 2

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

Score: 1

It can depend on the type of index you have 9 on the table.

Most B-tree indexes do not store 8 null entries. Bitmap indexes do store null 7 entries.

So, if you have:

select * from mytable where 6 mycolumn is null

and you have a standard 5 B-tree index on mycolumn, then the query can't use the 4 index as the "null" isn't in the index.

(If 3 the index is against multiple columns, and 2 one of the indexed columns is not null then 1 there will be an entry in the index.)

Score: 0

Create an index on that column.

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

ocdecio answered:

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

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

Score: 0

It's also worth checking whether Oracle's 3 statistics on the table are up to date. It 2 may not know that a full table scan will 1 be slower.

Score: 0

Oracle database don't index null values 3 at all in regular (b-tree) indexes, so it 2 can't use it nor you can't force oracle 1 database to use it.

BR

Score: 0

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

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

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

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

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

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

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

More Related questions