RE: USER DEFINED FUNCTIONS

Larry

Thanks for a fine and detailed info.

Yechiel Adar, Mehish Computer Services
adary_at_mehish.co.il

> -----Original Message-----
> From: Larry Elkins [SMTP:elkinsl_at_flash.net]
> Sent: Sun, March 10, 2002 4:13 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: USER DEFINED FUNCTIONS
>
> 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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: =?iso-8859-8?Q?=E0=E3=F8_=E9=E7=E9=E0=EC?=
  INET: adary_at_mehish.co.il

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).