RE: What is the best way to do this?

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Fri, 1 Apr 2011 18:33:05 -0400
Message-ID: <000301cbf0bc$c5a47b40$50ed71c0$_at_rsiz.com>

Six questions:

1. How many customers?
2. Once established, can a customer's data_map change?
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?
4. How do you receive transactions?
5. Do you have partitioning?
6. Do any of your customers actually use more than 255 columns?

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Michael Moore
Sent: Friday, April 01, 2011 2:55 PM
To: oracle-l_at_freelists.org
Subject: What is the best way to do this?  

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 - 17:33:05 CDT