MLPPI maintenance for Populated tables.

Database
Enthusiast

MLPPI maintenance for Populated tables.

Hello Carrie and other Teradata Gurus,

Can someone please help me with details related to a populated MLPPI table maintenance.My requirement is that we need to modify only the Level1 partition for the populated MLPPI table ( 500GB in size).The current partition is something like this:

PARTITION BY ( RANGE_N(End_Dt BETWEEN DATE '1997-01-07' AND DATE '1999-12-31' EACH INTERVAL '1' YEAR ,
DATE '2000-01-01' AND DATE '2007-12-31' EACH INTERVAL '84' DAY ,
DATE '2008-01-01' AND DATE '2011-01-31' EACH INTERVAL '7' DAY ),
CASE_N(
location_id = 10 ,
location_id = 20,
.......
......
);

My requirement is that i need to modify the partition to something like this and keep the level2 partition the same:

PARTITION BY ( RANGE_N(Date_Col BETWEEN DATE '1997-01-07' AND DATE '1999-12-31' EACH INTERVAL '1' YEAR ,
DATE '2000-01-01' AND DATE '2008-12-31' EACH INTERVAL '84' DAY ,
DATE '2009-01-01' AND DATE '2012-01-31' EACH INTERVAL '7' DAY ),
CASE_N(
loc_id = 10 ,
loc_id = 20,
.......
......
);

Any work around will be appreciated, i dont wont to end up in creating a new table with new partition defination, since the table size is huge (over 500GB).

Any comments will be appreciated.

Thanks
Javed
3 REPLIES
Senior Apprentice

Re: MLPPI maintenance for Populated tables.

Hi Javed,
this partitioning schema is a bad choice (if you have to modify it).

Of course you could change the partitioning using
ALTER TABLE ...
MODIFY PRIMARY INDEX
DROP RANGE ...
ADD RANGE ...

But this will result in a huge Transient Journal, because the data from 2007 to 2010 must be moved into different partitions. This is probably slower than a new table plus insert/select.

A better choice might be not to change 2008, just add 2012.
You might have to drop the last partial week around 2011-01-31 first.

Dieter
Enthusiast

Re: MLPPI maintenance for Populated tables.

Thanks Dieter for your feedback.

Since i am pretty much under the max partition limit for now, i will go with your suggestion of adding new partitions for 2011-2012 instead of altering any old partition definations.

In case if i want to modify any existing intermediate partition ( assume Level #1 ) what are my choices for a populated table.My understanding with MLPPI (populated table) is that we cannot modify/drop any of the existing intermediate partitions even if the data for that partition is moved to a temp table.

I get the following error "3732: The facility of altering the partitioning of a non-empty table in this case has not been implemented yet.

Thanks
Javed

Enthusiast

Re: MLPPI maintenance for Populated tables.

Hi Dieter,

Is there a way that i can identify empty partitions for a PPI and MLPPI tables.I suspect that some of the partitions defined doesnt have any data in them and i want to identify them and drop those empty partitions.Any expert opinion will be appreciated.

Thanks
Javed