Re: Function Based indexes

A copy of this was sent to "Billy Verreynne" <vslabs_at_onwe.co.za> (if that email address didn't require changing) On Fri, 21 May 1999 15:11:48 +0200, you wrote:

>Mike Burden wrote in message <374556BB.3354AB5B_at_capgemini.co.uk>...
>>Function based indexes seem really interesting (ok so I'm sad) but I
>>have seen very little written about them (i.e. Thomas Kyte's article).
><snipped>
>>Can anyone think of any other good uses.
>
>
>..Friday afternoon.. another one of those weeks... but let me think...
>hmmm...
>
>Stress testing the CPU?

Actually -- they will reduce the stress on the CPU. Think about it -- you

INSERT a row once
SELECT a row hundreds/thousands/millions of times.

Creating an index like:

create index myidx on emp(upper(ename))

means we will upper case ENAME once on insert and when you query:

select * from emp where upper(ename) = 'KING'

instead of doing upper once PER ROW (as we would without an index on upper), we will not even invoke UPPER for this query.

So, if you query every row 2 times in its life and you didn't have function based indexes and you have 1,000 records in the table -- you will have run the upper function 2*1,000*1,000 times (takes a full scan! so for every row we get 2,000 of em, we have to inspect 1,000 rows).

So, if you query every row 2 times in its life and you do have function based indexes and you have 1,000 records in the table -- you have run the upper function 1,000 times. Once per insert.

It gets even better if you replace UPPER with some function you write. The overhead of calling plsql from sql wouldn't hold up on a 1,000 row table (if we called the plsql function for each row) but if we use function based indexes -- the results can be pretty amazing.

>
>;-)
>Billy
>
>

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri May 21 1999 - 15:51:42 CDT