Re: performance question with function returning object type

Thanks -- I was going to try adding some counters etc. to prove that the function was being invoked multiple times, but I have a deadline so I had move on to an alternate approach. What I am doing now is returning a table type that contains slightly less aggregated data than my original function. Although I then have to subsequently do some simple summarizing of that information, it actually makes the function more general-purpose and the performance is much better. I have gone from over 3 minutes to get a set of data to under 2 seconds.

What prompted my first technique was Tom Kyte's "Effective Oracle" book, specifically the technique to avoid an outer join by using a scalar subquery that returns an object type (p 508 - 509). Ultimately I still need this in a scalar subquery, but I now concatenate the values into a single string value and substr() them out in the parent query (this technique is also described on p. 508) instead of using the object type technique.

I appreciate that you took the time to investigate this further... Allan Received on Fri Dec 17 2004 - 09:18:58 CST