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:
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:
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.
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.