Re: user-defined functions

Jonathan Gennick wrote:
>

[snip]
> It depends on where and how you use the function. Obviously
> there is some cost just to execute the code within the
> function, but that's not the real worry. The real worry is
> that you will inhibit Oracle's use of indexes. Consider the
> following query:
>
> select * from employee
> where emp_name = 'Gennick';
>
> If there is an index on emp_name, Oracle will likely use it.
> Here is the same query again, but with the UPPER function
> being used to make it case insensitive:
>
> select * from employee
> where UPPER(emp_name) = 'GENNICK';
>
> In this case, Oracle can't use an index. In order to
> evaluate UPPER(emp_name) properly, it must apply that
> function to each row in the table. That means a full table
> scan, where every record is read.
>

[snip]

I have wondered why this is the case. Why doesn't Oracle use the UPPER(...) function on the key values in the index; perhaps slower, but avoiding the full-table?

Thanks,

Geoff Houck
systems hk
hksys_at_teleport.com
http://www.teleport.com/~hksys Received on Thu Dec 31 1998 - 21:59:27 CST