All Forums Database
ruyeh00 9 posts Joined 09/13
11 Nov 2014
Is it possible to modify my Partition?

Hi,
We have a table that has been partitioned as the dataset will grow into the billions of rows.  It has a Primary Index, here is the bottom part of the show table which contains the partition statement:
PRIMARY INDEX (OFFER)
PARTITION BY ( RANGE_N(Week_ID  BETWEEN 201201  AND 201253  EACH 1 ,
201301  AND 201353  EACH 1 ,
201401  AND 201453  EACH 1
),CASE_N(
WK_RNG =  '01_04_13',
WK_RNG =  '05_08_13',
WK_RNG =  '09_12_13',
WK_RNG =  '13_16_13',
WK_RNG =  '17_20_13',
WK_RNG =  '21_24_13',
WK_RNG =  '25_28_13',
WK_RNG =  '29_32_13',
WK_RNG =  '33_36_13',
WK_RNG =  '37_40_13',
WK_RNG =  '41_44_13',
WK_RNG =  '45_48_13',
WK_RNG =  '49_52_13',
WK_RNG =  '01_04_14',
WK_RNG =  '05_08_14',
WK_RNG =  '09_12_14',
WK_RNG =  '13_16_14',
WK_RNG =  '17_20_14',
WK_RNG =  '21_24_14',
WK_RNG =  '25_28_14',
WK_RNG =  '29_32_14',
WK_RNG =  '33_36_14',
WK_RNG =  '37_40_14',
WK_RNG =  '41_44_14',
WK_RNG =  '45_48_14',
WK_RNG =  '49_52_14') );
 
Is it possible to alter the partition without having to drop and recreate the table?  Doing inserts back into a duplicate table with the updated partition is also proving difficult due to the dataset size.  It isn't possible to break the table up at this point.
 
Thanks in advance.

Raja_KT 1246 posts Joined 07/09
11 Nov 2014

You have not told much about the altering criteria :).
Here is an example:
http://forums.teradata.com/forum/database/modify-the-partition
Just for your information: You cannot use the ADD and DROP options to modify a partitioning expression for a table when the expression is based on a CASE_N function

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

ruyeh00 9 posts Joined 09/13
12 Nov 2014

Sorry, I need to modify one of the WK_RNG to '49_53_14', currently it says '49_52_14'.  Also wonder if its possible to add to the RANGE_N and/or CASE_N in the future in the event that I need to add more years and/or weeks to the partition?

ruyeh00 9 posts Joined 09/13
13 Nov 2014

If ADD / DROP isn't an option for CASE_N partitions, can someone recommend an alternative to dropping and recreating the table as it contains over 33 billion records in it.

You must sign in to leave a comment.