Re: Comparison of Function-Based and regular indexes

Connor McDonald wrote:
> Bruno wrote:
> >
> > 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
>
> Its predominantly related to the cost of the function. If its a low
> cost expression, then you'll hardly notice the difference. If its
(say)
> a custom plsql function that does 1000 things within in, then you'll
> certainly notice it.

However it would be good to compare like with like. Sure if the function does some complicated stuff within it then it will be slower than a regular index on a varchar2 column. But in many cases you probably should be comparing with a regular index on a varchar2 column in a table *that has a trigger defined on it that achieves the same logic*. I'd hazard a guess that in this case the insert/update timings would come out comparable.

In the classic example the function is just UPPER(COLUMN_NAME), but probably the solution for the app that didn't have the FBI would include a trigger that upper cased all data after insert or update of the column.

Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com Received on Thu Nov 11 2004 - 09:26:41 CST