Re: need some advice(you partition experts out there, this one is for you).

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 23 Apr 2004 18:10:01 +0100
Message-ID: <002201c42955$d2a47a10$7102a8c0@Primary>

It looks like you need to:

Create working table as select old data excluding

new number, year and qtr from old partition Insert append new data into working table. Index working table
Exchange.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

April 2004 Iceland http://www.index.is/oracleday.php June 2004 UK - Optimising Oracle Seminar July 2004 USA West Coast, Optimising Oracle Seminar August 2004 Charlotte NC, Optimising Oracle Seminar September 2004 USA East Coast, Optimising Oracle Seminar September2004 UK - Optimising Oracle Seminar

Original Message ----- From: <jtesta_at_dmc-it.com> To: <oracle-l_at_freelists.org> Sent: Friday, April 23, 2004 6:00 PM Subject: need some advice(you partition experts out there, this one is for you).

Ok here is the scenario:

we will have a table partitioned by list on a number, so far so good.

also as part of that table is a processing yr and qtr.

we will be doing the ETL thing on number, yr, qtr at a time.

here is our process:

get data, do the etl at number, yr and qtr at a time. we want to load into a temp table, build the bitmap index(es) on the temp table and then do a partition exchange, all doable up to this point.

we have data for older years already in the number (remember its list partitioned), we need the users to have access to the older data while we're processing and then want to do an exchange into the fact partition, but exchange is TRULY exchange, how can we basically append the new data into the exchange but w/o the overhead of the bitmap indexes killing us. We're trying to not drop the bitmaps as there will be 12 FK to dimensions.

We used to do range partitioning but this process to do the ETL is totally dynamic and builds partitions on the fly if need be, List partitioning is alot easier to add a partition then to do the split partition thing if the number falls in the middle.

any thoughts on this one would be greatly appreciated.

thanks, joe

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.
--

Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

Please see the official ORACLE-L FAQ: http://www.orafaq.com

Received on Fri Apr 23 2004 - 12:07:34 CDT