Home » RDBMS Server » Performance Tuning » From staging tables to datamart table -- how?
From staging tables to datamart table -- how? [message #188317] Thu, 17 August 2006 23:27 Go to next message
Messages: 194
Registered: January 2006
Senior Member
I have a very big problem here..

I have 6 staging tables. Each table has data for 6 months starting from Jan 2003.
- TabStage1 has data for Jan2003 to Jun2003
- TabStage2 has data for Jul2003 to Dec2003
- TabStage3 has data for Jan2004 to Jun2004
- TabStage4 has data for Jul2004 to Dec2004
- TabStage5 has data for Jan2005 to Jun2005
- TabStage6 has data for Jul2005 to Dec2005

I have a data mart table, TabMaster, into which I have to load all these (plus a join on some other tables). This table should be partitioned by month. I cannot load all 6 tables at the same time as it will heavily load the database. Now, this is what I am thinking:

1. Create a temporary partitioned master table for each staging table:
   Create table TabMaster1 (partitioned monthly from Jan2004 to Jun2003) 
     as Select from TabStage1, <SomeTable1, SomeTable2>..

   Create table TabMaster2 (partitioned monthly from Jul2004 to Dec2003) 
     as Select from TabStage2, <SomeTable1, SomeTable2>..

   Create table TabMaster6 (partitioned monthly from Jul2005 to Dec2005) 
     as Select from TabStage6, <SomeTable1, SomeTable2>..

2. Create the real master table by adding the partitions from the temporary master tables:
   Create table TabMaster
    (exchange partition / move partition from TabMaster1, TabMaster2..TabMaster6);

-- By the way, is this possible? If so, can you please let me know as I am not familiar with partition operations?

Is this a proper approach?? I would very much appreciate any feedback/suggestions as this problem is eating my head.. (and I want to have a restful weekend ahead.)..
Thanks in advance..

[Updated on: Thu, 17 August 2006 23:28]

Report message to a moderator

Re: From staging tables to datamart table -- how? [message #188338 is a reply to message #188317] Fri, 18 August 2006 02:23 Go to previous messageGo to next message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Yes it is possible. You can't exchange a partion from one table with a partition from another table, so you have to exchange it with a non-partitioned table, then exchange that with the target partitioned table (did that make sense?).

Why do you think doing it all in one hit would be too much load on the database? If you invalidate indexes and FKs, and use INSERT /*+APPEND*/ then it should be OK. The partition-exchange has the advantage that you can do it with NOLOGGING, but I would not have thought the INSERT would be too bad.

Ross Leishman
Re: From staging tables to datamart table -- how? [message #188361 is a reply to message #188338] Fri, 18 August 2006 03:50 Go to previous message
Messages: 194
Registered: January 2006
Senior Member
I just tried it, and you're right. I don't have to partition the TabMaster1..TabMaster6. Actually, the initial approach was INSERT /*+ APPEND */ but it's taking so much time. Just to give you an overview, creating one TabMaster1 takes an average of one hour. So 6 of them = 6 hours. Plus, don't forget the staging tables (TabStage1..TabStage6) which also takes 1 hour each to create. Thanks for the NOLOGGING option (I din't know that)..I will also use WITHOUT VALIDATION.
Previous Topic: Two questions about interviews
Next Topic: Creating Partitioned table faster than Non-partitioned
Goto Forum:

Current Time: Thu Aug 17 23:54:18 CDT 2017

Total time taken to generate the page: 0.14013 seconds