Modify partition range in nonempty tables

Database
Enthusiast

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.

Thanks in advance!

3 REPLIES
Teradata Employee

Re: Modify partition range in nonempty tables

Hi,

this is covered in the documentation, in the SQL Data Definition Language - Detailed Topics / Syntax and Examples chapters.  

You can search for "ADD RANGE", "DROP RANGE" keywords in the Advanced search to find those examples.

The documentation can be downloaded here:  http://www.info.teradata.com/Datawarehouse/eBrowseBy.cfm?page=TeradataDatabase.   Choose the database version on the top right corner, and then download either separate chapters, or - better - the whole User Documentation CD-ROM.

Regards,

Vlad.

Enthusiast

Re: Modify partition range in nonempty tables

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!

Teradata Employee

Re: Modify partition range in nonempty tables


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