| exchange partition [message #386321] |
Fri, 13 February 2009 11:31  |
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 #386909 is a reply to message #386422] |
Tue, 17 February 2009 04:25  |
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
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
