Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: USER DEFINED FUNCTIONS

RE: USER DEFINED FUNCTIONS

From: Larry Elkins <elkinsl_at_flash.net>
Date: Sun, 10 Mar 2002 06:13:21 -0800
Message-ID: <F001.004241D1.20020310061321@fatcity.com>


I agree, they can be useful, and, they can be a pain when used inappropriately. Having worked with a few applications that made *extensive* use of UDF's, here are some of the more common things I came across. Note that all these examples were tested against 8.1.7 before posting. Behavior may be different in other versions.

Select fGetFname(person_id),

       fGetMI(person_id),
       ....

You get the idea -- each of those functions hit a person table with the PK value to return a single attribute from the same row. And there are 10 to 15 calls to functions returning individual attributes from persons. This was functionally equivalent to having the persons table in the from clause 10 times and joining to each with the PK value to get each attribute. Either join to the table directly, or, have functions that return all the different types of expected combinations so that you only call it once. But used in the manner above is quite wasteful of resources.

And as Stephane mentioned, having a UDF treated as a correlated event can sometimes be a bad thing. For example, you are doing an aggregate on 10,000,000 rows, calling the function in the select, and thus the function does 10 million indexed lookups (via the function) into a 100 row table (cache hit ratio looks good ;-)). Might have been better off with an FTS and HJ in that case ;-) And the function doesn't have to have a SELECT in it, simply calling it more times than needed regardless of what it does is wasteful.

One way around this correlated approach, or simply calling a function too many times, is to use in-line views (when possible), and the NO_MERGE hint if necessary, to minimize calls to the function. Call the function at the lowest level of granularity where it can be resolved instead of after you have joined to 5 more tables and exploded into a lot more rows, and hence calls to the function. This can reduce the number of calls the function dramatically.

For example, assume the following two SQL statements where the value returned by the function is dependent upon the DEPTNO value. In the case of DEPT, we know there are only 4 values for DEPTNO:

Case 1:

select fFoo(D.Deptno) Foo,

       E.Deptno,
       E.Ename

>From Emp E, Dept D

Where D.Deptno = E.Deptno

Case 2:

select /*+ NO_MERGE(D) */

       D.Foo,
       E.Deptno,
       E.Ename

>From Emp E,
(Select fFoo(Deptno) Foo, Deptno Deptno From Dept) D

where d.deptno = e.deptno(+)

In Case 1, the function is going to be called once for each row returned by the query, 15 (15 rows returned when outer joining EMP and DEPT), yet we know there are only 4 distinct values for DEPTNO (and only 3 used in EMP). In case 2, we use an in-line view on just DEPT, calling the function once for each row in DEPT. *Then*, we join to EMP. We thus reduced the calls to the function. In the case above, NO_MERGE was used since the CBO wanted to merge the in-line view into the main query and would have treated it like the first query, calling the function 15 times. The NO_MERGE prevented that. You have to use this carefully, though. Connor McDonald show's a similar technique at http://www.oracledba.co.uk/tips/forcing_order.htm when dealing with it in a WHERE clause (touched on later).

Note that the technique above of using in-line views can be used for all kinds of good things, such as reducing sorts needed for group by operations by doing the sorting require of the group by operation at the lowest level possible before joining into a lot more rows. Jonathan Lewis illustrates another good use of in-line views at
http://www.jlcomp.demon.co.uk/inline_1.html.

Back to UDF's, here's one more example of how UDF's can be misused (and sometimes corrected). You might come across a situation where the UDF is referenced in the WHERE clause *and* the arguments to the UDF are constants, in other words, returning the same value for every row:

Where A.Col1 = UDF(1,2,3)
  and ...
      ...

Just had a case where the query was killed after a few hours and the function, and thus the SELECT statement in the function had been called a few hundred thousand times. Simply changing to the following syntax reduced it to only one call:

Where A.Col1 = (Select UDF(1,2,3) from dual)

Ok, so if the UDF in the WHERE clause does not return a constant, you can still think back to the prior in-line view example and find ways to call it at the lowest level of granularity where the value changes and then join back into the mainline query.

One other item of interest related to this is the order of predicate evaluation, touched on earlier on the reference to Connor's information. I haven't done a lot of testing on it so would be interested in what others have seen or done. Anyway, in the limited testing, a UDF was evaluated last in the list of predicates. Assume the following 4 examples:

select *
from emp
where sal = -123456
  and fempdname(deptno) = 'DALLAS';

select *
from emp
where fempdname(deptno) = 'DALLAS'
  and sal = -123456;

select /*+ ORDERED_PREDICATES */ *
from emp
where sal = -123456
  and fempdname(deptno) = 'DALLAS';

select /*+ ORDERED_PREDICATES */ *
from emp
where fempdname(deptno) = 'DALLAS'
  and sal = -123456;

In the case of the first 2 statements, regardless of whether the UDF was referenced first or last, the trace file indicated the select statement in the function was never called. In case 3, by using the ORDERED_PREDICATES, the function wasn't called. In case 4, though, the function's SQL statement was kicked off 14 times. Anyway, I need to do more thorough testing on this, but from the simple example above, it appears the UDF is evaluated last. Jonathan Lewis has an interesting case with multiple UDF's at http://www.jlcomp.demon.co.uk/where.html.

Sorry for the length of my response but hopefully it might help some folks deal more efficiently with UDF's. And maybe prompt some other examples?

Regards,

Larry G. Elkins
elkinsl_at_flash.net
214.954.1781

Regards,

Larry G. Elkins
The Elkins Organization Inc.
elkinsl_at_flash.net
214.954.1781

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Stephane
> Faroult
> Sent: Saturday, March 09, 2002 1:48 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: USER DEFINED FUNCTIONS
>
>
> "Jamadagni, Rajendra" wrote:
> >
> > select to_char(1) from dual;
> >
> > to_char is a user defined function (already built for you) by
> oracle. I am
> > yet to find someone who says UDF is a bad thing ...
> >
> > Raj
> > ______________________________________________________
> > Rajendra Jamadagni MIS, ESPN Inc.
> > Rajendra dot Jamadagni at ESPN dot com
> > Any opinion expressed here is personal and doesn't reflect that
> of ESPN Inc.
> >
> > QOTD: Any clod can have facts, but having an opinion is an art!
> >
> >
> ------------------------------------------------------------------------
> > Name: ESPN_Disclaimer.txt
> > ESPN_Disclaimer.txt Type: Plain Text (text/plain)
> > Encoding: 7bit
>
> I don't consider UDFs to be bad things per se. It's just what developers
> do out of them. It's just like triggers. A carefully written trigger can
> add less overhead than a regular index, for instance. That is, unless it
> executes queries of death. It's exactly the same stuff with UDFs. It all
> depends on how they are written. The only problem is that when they are
> used in the SELECT LIST they are called once for each row returned, like
> say a correlated subquery. In the hands of your average,
> middle-of-the-bell-curve developer, it can become a lethal weapon.
> --
> Regards,
>
> Stephane Faroult
> Oriole Ltd

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Larry Elkins
  INET: elkinsl_at_flash.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Sun Mar 10 2002 - 08:13:21 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US