Home » RDBMS Server » Performance Tuning » exchange partition (oracle 10g , unix)
exchange partition [message #386321] Fri, 13 February 2009 11:31 Go to next message
dr46014
Messages: 49
Registered: February 2007
Member
Hi
I am using a target table which is partitioned on the basis of month_code and sub partitioned on the basis of client_id.The table has index on two columns account_nbr and cust_id.The index is b-tree local index.I am trying to use partition exchange method to load data in the target table.Below is the naming convention of table space.
For each month code partition it is
TBL_NAME_TBLSPS_200901_DATA
TBL_NAME_TBLSPS_200902_DATA
TBL_NAME_TBLSPS_200903_DATA like this.
and for index it is
TBL_NAME_TBLSPS_200901_INDX
TBL_NAME_TBLSPS_200902_INDX
TBL_NAME_TBLSPS_200903_INDX

So for exchange partition i have 2 options:

Option1:
1.Drop and Re create the exchange table(partitioned on client_id) each time with the dynamic table space assigned to the table.
2.Load the data into exchange table.
3.Create index on exchange table.The create index SQL will contain the dynamic table space name in it.
4.Doing exchange partitiom.

Option2:
1.Create a initial exchange table with a table space specific to that month.
2.Move table space each month before loading activity.This will contain dynamic table space name.
3.Drop existing index.
4.Create index on the exchange table.The create index SQL will contain the dynamic table space name in it.
5.Doing partition exchange.

I am not sure which is the efficient method .. droping and creating table every time or moving table space.

If there is any other alternative please suggest.
Re: exchange partition [message #386419 is a reply to message #386321] Sat, 14 February 2009 12:04 Go to previous messageGo to next message
dr46014
Messages: 49
Registered: February 2007
Member
any suggestions ??
Re: exchange partition [message #386421 is a reply to message #386321] Sat, 14 February 2009 12:08 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>I am not sure which is the efficient method .. droping and creating table every time or moving table space.
After benchmark testing both, which is/was faster?
Re: exchange partition [message #386422 is a reply to message #386421] Sat, 14 February 2009 12:11 Go to previous messageGo to next message
dr46014
Messages: 49
Registered: February 2007
Member
Dropping and re creating table took comparatively less time.But the time difference is very low.
Re: exchange partition [message #386909 is a reply to message #386422] Tue, 17 February 2009 04:25 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If the table is truncated before you start then the move should not be a problem and there should be no significant difference. If you leave data in, then the MOVE will generate redundant IO and Method 1 will be faster.

Ross Leishman
Re: exchange partition [message #386924 is a reply to message #386321] Tue, 17 February 2009 05:13 Go to previous message
Alessandro Rossi
Messages: 166
Registered: September 2008
Location: Rome
Senior Member
Ops

[Updated on: Tue, 17 February 2009 05:16]

Report message to a moderator

Previous Topic: Perfoemnace Tunning
Next Topic: Not using Index
Goto Forum:
  


Current Time: Tue Dec 03 04:55:32 CST 2024