Re: user-defined functions

On Wed, 30 Dec 1998 13:57:52 +0900, "Young-gyun Koh" <immortal_at_in4bank.com> wrote:

>I was told that the use of user-defined functions in queries may slow down
>the performance.
>
>Is that true? then, that's why?

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.

So that's the issue, that functions might prevent an index from being used. Here are two situations where functions should not matter:

	select * from employee
		where emp_name = upper('Gennick');

	select upper(emp_name) from employee;

In the first case, the function is applied to a literal, not to a column of the table. In the second case, the function is applied to a value being returned, which is not part of the where clause.

If you do need to use a function in the where clause of a query, I recommend taking teh time to run an EXPLAIN PLAN on that SELECT statement. EXPLAIN PLAN will tell you exactly how Oracle intends to execute the query, and you will be able to see if any unwanted table scans are ocurring.

Jonathan Received on Thu Dec 31 1998 - 18:20:08 CST