Hi Stephan, I currently have my Function in a package. The function calls several sub-functions. I've used RESULT_CACHE all all sub-functions where it makes sense to do so. You are correct, it does save a significant number of queries. In my testing, without RESULT_CACHE, each sub-function would be called 30,271 times. WITH Result_Cache, one of the sub-functions was only called 7,126 time and another sub-function was called only 661 times. Quite a savings. Regards, Mike On Mon, Apr 4, 2011 at 1:19 PM, Stephane Faroult <sfaroult@xxxxxxxxxxxx>wrote: > Michael, > > What you can possibly do is package your function, and record in a package > array or whatever, dynamically, attributes that are usually called together > (if customer_number = customer number of the previous call, then the virtual > column name probably has some "affinity" with the previous one). > Whenever you fetch data for a customer, check whether the customer_number > is the same one as the previous one you have seen, if this is the case > whether by chance you already hold the value in a buffer. If not, fetch all > the values that you know to have some affinity with the one that is required > at once. > Should avoid a significant number of queries. > > SF > > > > On 04/04/2011 07:20 PM, Michael Moore wrote: > > Hi Yechiel, > > I can't really do that because I am limited to modification only within my > PLSQL FUNCTION. I can not change the program that calls my function. > > > My task is to optimize/rewrite an *existing function* which looks like: > > FUNCTION get_attribute_value (customer_number IN NUMBER, > virtual_column_name IN VARCHAR2) > returns varchar2; > > -- where the *return value* is the value of *one* of ATTR001 thru > ATTR201. > > My function does not "know" *all* of the ATTRibute names, it only know > the ONE that it is currently processing. > > There might be, for example, 31, 37 or 121 attributes that have a mapping > defined, but maybe only 1 of those attributes is required for pricing. For > example: > > abc_key = 778899 > customer # 12345 > attribute07 = 3.5 -- student GPA > attribute121 = 95050 -- zip code > attribute129 = Agriculture -- program of interest > attribute134 = 25 -- age > > Now lets assume that for this customer we have the business rule that > says: > If the student's age > 23 charge the customer $21 > If the student's age <= 23 charge the customer $29 > > My function will be passed (778899,'student_age') > My function will SELECT customer# from ABC where abc_key = 778899; > My function will figure out that for customer # 12345, 'student_age' is > stored in attribute134. > My function will select attribute134 from ABC where abc_key = 778899; > My function will return '25' > > attribute07, 121 and 129 are completely irrelevant to my process as they > have no affect on the price. > > Thanks for your help, > > Regards, > Mike > > > > > > > > > > On Sun, Apr 3, 2011 at 11:02 PM, Yechiel Adar <adar666@xxxxxxxxxxxx>wrote: > >> Based on your answers I think I will do this: >> >> 1) Create a new table that contain the customer number and the 5 >> attributes that you need for each customer. >> I mean, do a pivot on the data in data map and create one row for >> each customer containing all the 5 fields you need. >> You will need to recreate, or update, this table whenever the data >> map table changed or when you add or delete a customer. >> If you have, in data map, old customers that has no data in ABC then >> you can filter out their mapping in this step. >> >> 2) Read the new table. ordered by customer id, and construct dynamic sql >> to get only the fields you need from ABC. >> >> This way, you do not read the big table and you accessed the fact table >> only once for each customer and you save, big time, on network and database >> work to put all the fields in your buffer. >> This method also allow you to use bulk read for data map, saving more >> time. >> >> You did not mentioned what partitioning you have, but if you partition >> data map according to the field meaning, >> I mean a partition for all "credit ratings", a partition for all "last >> order" etc, >> You can replace the the table in step one with select the will bring you >> efficiently only the rows you need from the big table. >> If you have old data map for customers that has no rows in ABC, you can >> do: >> select * from data_map >> where field_description in ('credit rating','last order') >> and customer_id in (select customer_pk from ABC); >> >> Yechiel Adar >> Israel >> >> >> On 02/04/2011 02:03, Michael Moore wrote: >> >> Six questions: >> >> 1) How many customers? >> >> 26,000 >> >> 2) Once established, can a customer’s data_map change? >> >> it's very rare >> >> 3) If the answer to #2 is no, or only vary occasionally and with a >> defined maintenance event to reorganize that customer’s data, how many >> different data_map rows do you have? >> >> 1,700,000 >> 5) Do you have partitioning? >> >> yes >> >> >