Using a function-based index to get fast access to NULL table columns

One of our clients was having performance problems with one of their queries and it was rewarding to be able to solve the problem with knowledge recently gained at the AMIS 7UP work shop. The 7UP work shop introduces handy new SQL features that were introduced by Oracle from versions 7.2 up until version 10g.

The problem:

Have a table in which a value can be found in one of two columns (either in column “enameA�? OR in column “enameB�?). This means that for each row in the table, one of the columns (“enameA�? or “enameB�?) has a NULL value and the other has a value. Relational databases have the inability to create an index on a NULL column and therefore if you were to create the following index, all the employees that have a NULL value for their name would be ignored:

create index idx_emp_name
on emp (enameA);

So the following query asks “where enameA is NULL�?, and Oracle performs an unnecessary full-table scan even though we have an index on the enameA column.

select enameA,
enameB
from emp
where nvl(enameA, enameB) = ‘John’;

To get around this problem one can create a function-based index using the null value built-in SQL function to index on the NULL columns.

create index idx_emp_name
on emp (nvl(enameA, enameB));

analyze index srs_contacten_relatie_idx validate structure;

Now the same query will use the index and greatly improve the speed of the query.

One thing that must be remembered is that one uses the same syntax in the where clause as was used in creating the index. For example, if in our case we would have used “where nvl(trim(enameA), enameB)�?, because we add the extra “trim�? to the where clause, the index would not be used. However the use of aliases is not a problem e.g. nvl(emp.enameA, emp.enameB)