We had a client that was having performance problems with one of their queries and it was rewarding to be able to solve the problem with knowledge recently gained from the Amis 7UP work shop. The 7UP work shop presented handy new SQL features that were introduced by Oracle from versions 7.2 up until version 10g.
The problem:
I had a table in which a value could be found in one of two columns (either in column “enameA� OR in column “enameB�). This meant that for each row in the table, one of the columns (“enameA� or “enameB�) had a NULL value and the other had 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 solve this problem we create a function-based index.
create index idx_emp_name
on emp (nvl(enameA, enameB));
analyze index srs_contacten_relatie_idx validate structure;
The same query will now use the index and greatly improve the speed of the query.
One thing that must be remembered is that one must use 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)
