Re: What is the best way to do this?

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

4) How do you receive transactions?

Transactions comes from servers on the internet.

5) Do you have partitioning?

yes

6) Do any of your customers actually use more than 255 columns?

for my purposes, 5 columns is about the max that I am interested in but only 1 per call to my sub function.

Mark, hopefully you saw my reply to Stephane. From that you can see that I am working on a single sub function of an over all batch process.I think that post will make the scope/limitations of my project more clear.

Regards,

Mike

On Fri, Apr 1, 2011 at 3:33 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

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

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