OraFAQ Forum: Performance Tuning » Staging table from partitioned table

Home » RDBMS Server » Performance Tuning » Staging table from partitioned table

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

Staging table from partitioned table [message #187908]

Wed, 16 August 2006 05:58

a_developer
Messages: 194
Registered: January 2006

Senior Member

I have a staging table, TabStage, which I create using CTAS by selecting from 3 tables TabA, TabB, TabC.
TabA is range-partitioned on trx_date - monthly. Say 2004_01 for Jan 2004, 2004_02 for Feb 2004, so on and so forth.

When creating the staging table, I have a condition that says 'where trxn_date >= '01-Jan-2004'. I noticed that when I limit the range for only one month, that is, 'where trxn_date >= '01-Jan-2004 and trxn_date < '01-Feb-2004' - it executes for only 5 minutes. If I limit the range for 6 months, it executes for nearly an hour.

I am planning to create 24 temporary tables as a staging table for each partition:

Create Table temp_2004_01 as
select ... 
  from tabA partition (2004_01) a,
       tabB, tabC
 where trxn_date >= '01-Jan-2004'; 

Create Table temp_2004_02 as
select ... 
  from tabA partition (2004_02) a,
       tabB, tabC
 where trxn_date >= '01-Jan-2004'; 

...

Create Table temp_2006_12 as
select ... 
  from tabA partition (2006_12) a,
       tabB, tabC
 where trxn_date >= '01-Jan-2004';

and then create TabStage as:

Create Table TabStage as
select * from temp_2004_01
UNION
select * from temp_2004_02
UNION
...
UNION
select * from temp_2006_12;

Is this a good approach? Or anything better than this that you can think of?? Thanks in advance.

Report message to a moderator

Re: Staging table from partitioned table [message #187928 is a reply to message #187908]

Wed, 16 August 2006 06:54

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

1. Check EXCHANGE PARTITION option.
2. Why don't you use NOLOGGING in CTAS?

Report message to a moderator

Re: Staging table from partitioned table [message #187945 is a reply to message #187928]

Wed, 16 August 2006 07:33

a_developer
Messages: 194
Registered: January 2006

Senior Member

I am actually using NOLOGGING and PARALLEL option in CTAS. I just missed to include them.

I don't think EXCHANGE PARTITION will help because it's not a direct copy from TabA to TabStage. TabStage has to be created from 3 tables: TabA, TabB and TabC.

Report message to a moderator

Re: Staging table from partitioned table [message #188063 is a reply to message #187945]

Wed, 16 August 2006 21:55

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

If one of TabB or TabC is very large, then you are probably lucky that a single partition of TabA is small enough to hash in memory for a HASH JOIN.

If a join contains two very large tables (such that neither can be hashed into memory), then Oracle must bust them both up into partitions (different kind of partition) based on the join key. This uses heaps of TEMP space, performs heaps of I/O, and takes heaps longer.

Your partition by partition approach avoids this nicely, but you could probably do it all in one query, and also avoid a sort by replacing the UNIONs with UNION ALL.

Create Table TabStage as
select ... 
  from tabA partition (2004_01) a,
       tabB, tabC
 where trxn_date >= '01-Jan-2004'
UNION ALL
Create Table temp_2004_02 as
select ... 
  from tabA partition (2004_02) a,
       tabB, tabC
 where trxn_date >= '01-Jan-2004'
UNION ALL
...

Ross Leishman

Report message to a moderator