Oracle Index Null Values

| Posted by watashii | Filed under Database, Programming

When creating Oracle indexes, Oracle ignores the NULL values.  For example, the following index was created over “emp_name” column.

create index emp_name_idx
on employees (emp_name);

Querying for a NULL value on the indexed column would yield a full table scan.

select emp_name from employees where emp_name is NULL;

So how can we make Oracle perform a fast indexed search?

The solution is to index NULL values with a function-based index.  The following example replaces all the NULL values with a string named ‘null’ (note, this can be any arbitrary string).  This allows the NULL to be included as if it was a real value.

create index emp_name_idx
on employees (nvl(emp_name,'null'));

Tags: , ,

Read more | Comments (0) | September 21st, 2008