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
<mailto: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
