OraFAQ Forum: Performance Tuning » From staging tables to datamart table -- how?

Home » RDBMS Server » Performance Tuning » From staging tables to datamart table -- how?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

From staging tables to datamart table -- how? [message #188317]

Thu, 17 August 2006 23:27

a_developer
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

rleishman
Messages: 3728
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

Report message to a moderator

Re: From staging tables to datamart table -- how? [message #188361 is a reply to message #188338]

Fri, 18 August 2006 03:50

a_developer
Messages: 194
Registered: January 2006

Senior Member

Ross,
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.

Report message to a moderator

Previous Topic:	Two questions about interviews
Next Topic:	Creating Partitioned table faster than Non-partitioned

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed May 01 23:53:43 CDT 2024