Home » RDBMS Server » Performance Tuning » Staging table from partitioned table
Staging table from partitioned table [message #187908] Wed, 16 August 2006 05:58 Go to next message
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.

Re: Staging table from partitioned table [message #187928 is a reply to message #187908] Wed, 16 August 2006 06:54 Go to previous messageGo to next message
michael_bialik
Messages: 611
Registered: July 2006
Senior Member
1. Check EXCHANGE PARTITION option.
2. Why don't you use NOLOGGING in CTAS?
Re: Staging table from partitioned table [message #187945 is a reply to message #187928] Wed, 16 August 2006 07:33 Go to previous messageGo to next message
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.
Re: Staging table from partitioned table [message #188063 is a reply to message #187945] Wed, 16 August 2006 21:55 Go to previous messageGo to next message
rleishman
Messages: 3724
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
Re: Staging table from partitioned table [message #188064 is a reply to message #187908] Wed, 16 August 2006 21:57 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>where trxn_date >= '01-Jan-2004';
The characters to the right of the equal sign are STRINGS not DATEs
use TO_DATE
Re: Staging table from partitioned table [message #188196 is a reply to message #188064] Thu, 17 August 2006 07:17 Go to previous message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Ross, I'll probably get 6 months of data rather than 'monthly' as you suggested. Thanks to you and to anacedent and michael.


Previous Topic: Dictionary Managed to Locally Managed Tablespace
Next Topic: Inserting huge amount of data into Oracle database
Goto Forum:
  


Current Time: Wed Dec 07 18:46:44 CST 2016

Total time taken to generate the page: 0.13508 seconds