15 November 2006

Functions Can Trump Indexes

It seems that I'm dense enough to simply read over the first sentence of this post (which I linked to earlier) and not bother to think what it means. Last night I read about it in the 10g DBA Handbook and it stuck, probably because they focused on it with some examples.

Using a function on a B*Tree (normal) indexed column in your WHERE clause means that Oracle won't use that index. For example:

select * from emp
where UPPER(lastname) = 'JONES';

will most likely result in a full table scan, despite an index on lastname. However, you could create a function-based index on UPPER(lastname), like this:

create index emp_upper_lastname on
emp(upper(lastname));

Of course, the whole point of Jeff's post was to describe an alternative to using a function-based index by using UPPER() check constraints that the CBO can utilize.

The world becomes clearer every day.

No comments:

Post a Comment