How to use a function-based index on a column that contains NULLs in Oracle 10+?

Question

Lets just say you have a table in Oracle:

CREATE TABLE person (
  id NUMBER PRIMARY KEY,
  given_names VARCHAR2(50),
  surname VARCHAR2(50)
);

with these function-based indices:

CREATE INDEX idx_person_upper_given_names ON person (UPPER(given_names));
CREATE INDEX idx_person_upper_last_name ON person (UPPER(last_name));

Now, given_names has no NULL values but for argument's sake last_name does. If I do this:

SELECT * FROM person WHERE UPPER(given_names) LIKE 'P%'

the explain plan tells me its using the index but change it to:

SELECT * FROM person WHERE UPPER(last_name) LIKE 'P%'

it doesn't. The Oracle docs say that to use the function-based index will only be used when several conditions are met, one of which is ensuring there are no NULL values since they aren't indexed.

I've tried these queries:

SELECT * FROM person WHERE UPPER(last_name) LIKE 'P%' AND UPPER(last_name) IS NOT NULL

and

SELECT * FROM person WHERE UPPER(last_name) LIKE 'P%' AND last_name IS NOT NULL

In the latter case I even added an index on last_name but no matter what I try it uses a full table scan. Assuming I can't get rid of the NULL values, how do I get this query to use the index on UPPER(last_name)?

Answer 1

The index can be used, though the optimiser may have chosen not to use it for your particular example:

SQL> create table my_objects
  2  as select object_id, object_name
  3  from all_objects;

Table created.

SQL> select count(*) from my_objects;
  2  /

  COUNT(*)
----------
     83783


SQL> alter table my_objects modify object_name null;

Table altered.

SQL> update my_objects
  2  set object_name=null
  3  where object_name like 'T%';

1305 rows updated.

SQL> create index my_objects_name on my_objects (lower(object_name));

Index created.

SQL> set autotrace traceonly

SQL> select * from my_objects
  2  where lower(object_name) like 'emp%';

29 rows selected.


Execution Plan
----------------------------------------------------------

------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |    17 |   510 |   355   (1)|
|   1 |  TABLE ACCESS BY INDEX ROWID| MY_OBJECTS      |    17 |   510 |   355   (1)|
|*  2 |   INDEX RANGE SCAN          | MY_OBJECTS_NAME |   671 |       |     6   (0)|
------------------------------------------------------------------------------------

The documentation you read was presumably pointing out that, just like any other index, all-null keys are not stored in the index.