All Forums Database
Kbos 20 posts Joined 04/13
16 Jul 2013
Modify the partition

Hi Everyone,
 
I have a table which has a partition by a date field as it follows:

PARTITION BY RANGE_N(RestoreDate  BETWEEN DATE '2008-04-01' AND DATE '2012-12-31' EACH INTERVAL '1' DAY ,
 NO RANGE);

And I would like to extend the partition, so the RestoreDate would start from '2005-01-01' to '2013-12-31',  How can I achieve this? Do I need to recreate the table? Do I need to drop the partition first and then create the new one? Any suggestions?
 
Thanks in advance!

Dixxie 58 posts Joined 12/10
17 Jul 2013

Hi Kbos,
Make a copy of your table and then try this (prior replace db_name and table_name ) :
DROP STAT    db_name.table_name  COLUMN(PARTITION);
ALTER TABLE  db_name.table_name  MODIFY PRIMARY INDEX ADD RANGE BETWEEN DATE '2005-01-01' AND DATE '2008-03-31' EACH INTERVAL '1' DAY;
ALTER TABLE  db_name.table_name  MODIFY PRIMARY INDEX ADD RANGE BETWEEN DATE '2013-01-01' AND DATE '2013-12-31' EACH INTERVAL '1' DAY;         
COLLECT STATISTICS ON db_name.table_name   COLUMN (PARTITION) ;
Regards.
 

Kbos 20 posts Joined 04/13
17 Jul 2013

Dixxie, thanks so much for your response :) it worked :)

You must sign in to leave a comment.