What is the best way to do this?
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 1 Apr 2011 11:54:45 -0700
I have a table with some specifically-named columns and a group of 210
generic column. For example:
table abc
customer_num
data_map_fk
attr001
attr002
attr003
attr004
So, in the example above, data_map_fk points to another table (say
DATA_MAP) which, in essence, tells you the meaning of attr001 thru attr004
for the given customer.
In this way, Customer A's *credit rating* might be in attr002, while
customer B's *credit rating* might be in attr004.
Unfortunately, in reality, table ABC has 210 generic ATTRnnn columns each
VARCHAR2(4000); ABC represents our core business transaction and we process
100's of 1000's of them. Most of the time, as we batch process these
transactions, we are only interested in 4-5 of the 210 ATTRnnn columns but
we don't know which 4-5 until we look up the DATA_MAP.
Now at this point you may be tempted to critique this design however this is
the GIVEN I have to work with.
I have tried the following two approaches to obtaining the values from the
target attributes:
1. The dynamic SQL approach
1. select data_map_fk from ABC
2. select attribute_info from DATA_MAP using data_map_fk
3. EXECUTE IMMEDIATE (select || targ_attr_name|| from ABC
2. associative array
examp:
FETCH cur_ABC
INTO data_map_fk,
m ('ATTRIBUTE01'),
m ('ATTRIBUTE02'),
m ('ATTRIBUTE03'),
m ('ATTRIBUTE04'),
m ('ATTRIBUTE05'),
m ('ATTRIBUTE06'),
m ('ATTRIBUTE07'),
m ('ATTRIBUTE08'),
...
m ('ATTRIBUTE210')
where abc_key = customer_num; -- customer_num is primary key
1. Read entire ABC into an associative array
2. select attribute_info (column_name) from DATA_MAP using data_map_fk
3. target_data_value := m(column_name)
Solution 1 has the disadvantage of needing to SELECT on ABC two times and
needs to use DYNAMIC SQL
Solution 2 seems to be a little slow, maybe due to loading the array or the
transfer of large amounts of data.
So, questions:
1) is there a better approach which I have not considered?
2) is there a way to make Solution 2 run faster?
3) any other thoughts?
Regards,
Mike
Other related posts:
