Re: What is the best way to do this?

  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_at_inter.net.il
> <mailto:adar666_at_inter.net.il>> 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
>>
>>
>

--
http://www.freelists.org/webpage/oracle-l