 How to improve the performance of "creating partition". [message #364430] |
Mon, 08 December 2008 07:38  |
abs_amit
Messages: 59
Registered: January 2006
Location: delhi
|
Member |
|
|
hi,
i have a table with huge partition of 200GB. and now we are splitting this partition in to new partitions (by month).
But its taking lot of time to perform that.
we are planning to create around 10 partition and creating first partition itself is taking around 10 hours.
just wanted to know, is that normal?
Database performance looks fine and Server too as well.
there is no blocking or locking session on the same object.
my server has four CPU's. should i use Parallelism to enhance the performance or any other suggestion to boostup the performance ?
any comments on the same are highly appreciated?
=============================================
alter table <table_name> split partition <parition_name>
at (to_date('2008-09-01', 'yyyy-mm-dd'))
into (partition <new_partition>, partition <partition_name)
===================================================
[Updated on: Mon, 08 December 2008 08:22] Report message to a moderator |
|
|
|
| Re: How to improve the performance of "creating partition". [message #364505 is a reply to message #364430] |
Mon, 08 December 2008 22:00  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
Splitting partition containing a huge amount of data will be always slow, as data belonging to different new partitions have to be separated physically.
Much more better way would be:
1) Create the empty table with the same structure as the original table
2) Exchange splitted partition with the new table
3) Split the partition on the original table (as it is empty, it will be fast)
4) INSERT SELECT into the original table from the new table (use APPEND hint, you may play with parallelism).
5) Drop or truncate (if it needs to be done more times) the new table
|
|
|
|
|
|
| Re: How to improve the performance of "creating partition". [message #364851 is a reply to message #364842] |
Wed, 10 December 2008 04:53 |
abs_amit
Messages: 59
Registered: January 2006
Location: delhi
|
Member |
|
|
Thanks for the really good suggestions.
But there are few constraints in implementing them:
1. the partition which we are planning to split is MAXVALUE partition.
2. partition size is around 210GB. and i have only 150GB free on the diskgroup. it means i can not go for the option (Creating new table).
any other idea keeping above two things in focus ?
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
