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 ,
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!
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) ;