Received: (qmail 26132 invoked from network); 4 Apr 2011 01:10:34 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-85-25-126-90.inaddr.intergenia.de with SMTP; 4 Apr 2011 01:10:23 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 36990E02217; Mon, 4 Apr 2011 02:10:22 -0400 (EDT) X-Virus-Scanned: Debian amavisd-new at localhost.localdomain Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id vBySVAqPiZZ7; Mon, 4 Apr 2011 02:10:21 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4A121E02030; Mon, 4 Apr 2011 02:09:37 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 04 Apr 2011 02:08:55 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id A67DCE01E04 for ; Mon, 4 Apr 2011 02:08:54 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id I8BJ9PB2sObd for ; Mon, 4 Apr 2011 02:08:54 -0400 (EDT) Received: from mtaout20.012.net.il (mtaout20.012.net.il [80.179.55.166]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 27194E01B3A for ; Mon, 4 Apr 2011 02:08:53 -0400 (EDT) Received: from conversion-daemon.a-mtaout20.012.net.il by a-mtaout20.012.net.il (HyperSendmail v2007.08) id <0LJ4005006DCL600@a-mtaout20.012.net.il> for oracle-l@freelists.org; Mon, 04 Apr 2011 09:08:34 +0300 (IDT) Received: from [192.168.0.39] ([212.68.138.137]) by a-mtaout20.012.net.il (HyperSendmail v2007.08) with ESMTPA id <0LJ40057I6E6G920@a-mtaout20.012.net.il> for oracle-l@freelists.org; Mon, 04 Apr 2011 09:08:31 +0300 (IDT) Date: Mon, 04 Apr 2011 09:02:54 +0300 From: Yechiel Adar Subject: Re: What is the best way to do this? In-reply-to: X-012-Sender: adar666@inter.net.il Cc: oracle-l@freelists.org Message-id: <4D995F0E.8080006@inter.net.il> MIME-version: 1.0 Content-Type: multipart/alternative; boundary="Boundary_(ID_vFUsSk6xsL7kEZ6VFTBRVw)" References: <000301cbf0bc$c5a47b40$50ed71c0$@rsiz.com> User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.15) Gecko/20110303 Lightning/1.0b2 Thunderbird/3.1.9 X-archive-position: 35469 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: adar666@inter.net.il Precedence: normal Reply-To: adar666@inter.net.il List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l --Boundary_(ID_vFUsSk6xsL7kEZ6VFTBRVw) Content-Type: text/plain; charset=windows-1252; format=flowed Content-transfer-encoding: 8BIT 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 > > --Boundary_(ID_vFUsSk6xsL7kEZ6VFTBRVw) Content-Type: text/html; charset=windows-1252 Content-transfer-encoding: 8BIT 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


--Boundary_(ID_vFUsSk6xsL7kEZ6VFTBRVw)-- -- http://www.freelists.org/webpage/oracle-l