Re: Comparison of Function-Based and regular indexes

"Bruno" <brunoterrone_at_hotmail.com> wrote in message news:QECkd.26907$km5.1496675_at_news20.bellglobal.com...
> Hi,
>
> Can someone point me to a resource or share their experience on this
subject?
>
> What is the difference in response time for SELECT, INSERT and UPDATE on
a table with regular index on a varchar2 column (all values uppercase) and the same table with the same index but this time function-based, with UPPER function on the same column (with mixed case values).
>
> Will INSERT and UPDATE operations be slower with the function-based
index? I would assume they should be, but am having hard time finding any resources. I am about to run some benchmarks on this, but am looking for a third-party opinion :-)
>
>
>
> Thanks!
>
> Bruno
>
>

I've used Function based indexes especially the upper on column. Sure, you re going to use some more CPU to do an insert or update. On the other hand, it seems pretty minor and not noticeable. Also you are making the application simpler - you don't have to search on the mirrored column and you don't have to write a trigger to keep that column up to date and you don't have to store that extra data.

I would use function based indexes.
Jim Received on Thu Nov 11 2004 - 00:54:37 CST