What is the best way to do this?

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Fri, 1 Apr 2011 11:54:45 -0700
Message-ID: <AANLkTimJOTZ6g6fMrRxp=pcDcpDxzFOePD6koFwS3jJh_at_mail.gmail.com>

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
2. select data_map_fk from ABC
3. select attribute_info from DATA_MAP using data_map_fk
4. EXECUTE IMMEDIATE (select || targ_attr_name|| from ABC
5. 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

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

Received on Fri Apr 01 2011 - 13:54:45 CDT