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).
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.
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.