Larry Thanks for a fine and detailed info.
Yechiel Adar, Mehish Computer Services [EMAIL PROTECTED] > -----Original Message----- > From: Larry Elkins [SMTP:[EMAIL PROTECTED]] > 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 > [EMAIL PROTECTED] > 214.954.1781 > > Regards, > > Larry G. Elkins > The Elkins Organization Inc. > [EMAIL PROTECTED] > 214.954.1781 > > > -----Original Message----- > > From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]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: [EMAIL PROTECTED] > > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED] 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: [EMAIL PROTECTED] (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).
