如下内容来源于网络，供参考：
--begin itlife365
HOw do you ALTER table add partitions on a multilevel partitioned table?
ALTER TABLE Table_Name MODIFY PRIMARY INDEX (col1,col2,col3) ADD RANGE BETWEEN DATE '2013-01-01' AND DATE '2013-12-31' EACH INTERVAL '1' MONTH;
*****************
forums.teradata#com/forum/database/modify-partition-range-in-nonempty-tables
Modify partition range in nonempty tables
Hi,
Is it possible to modify partition range in nonempty tables using alter table statement or by any other methods. If so, please share the syntax.
****************************
Hi,
Here the scenario, I have table partitioned by two columns(effective_Start_date and last_updated_date) and the table have data. partition range for effective start date is from 2008- 01-01 to 2013-12-01 each 1 month interval. My request is to modify the partition range from 2006-01-01 to 2013-12-01 without deleting the data. Please let me know is it possible in teradata 13.10V.
Many Thanks!
********************
create table test_ppi (
pi_field integer,
effective_Start_date date,
last_updated_date date
)
PRIMARY INDEX (pi_field)
PARTITION BY (
RANGE_N(effective_Start_date between date '2008-01-01' and date '2013-12-01' each interval '1' month),
RANGE_N(last_updated_date between date '2008-01-01' and date '2013-12-01' each interval '1' month)
);
insert into test_ppi values (1,date'2008-01-01',date'2008-01-01');
insert into test_ppi values (2,date'2010-01-01',date'2010-01-01');
show table test_ppi;
-- old
alter table test_ppi modify primary index
add range between date '2006-01-01' and '2007-12-31' each interval '1' month;
show table test_ppi;
-- new
************************************
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!
*****************
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.
end--itlife365
