Database
Enthusiast

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.

Tags (3)
3 REPLIES
Enthusiast

Re: Is it possible to modify my Partition?

You have not told much about the altering criteria :).

Here is an example:

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

Enthusiast

Re: Is it possible to modify my Partition?

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?

Enthusiast

Re: Is it possible to modify my Partition?

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.