Adding Fragment Expressions

Adding a fragment expression to the fragmentation list in an expression-based distribution scheme can relocate records from existing fragments into the new fragment. When you add a new fragment into the middle of the fragmentation list, the database server reevaluates all the data existing in fragments after the new fragment. The next example shows the original expression definition:

FRAGMENT BY EXPRESSION c1 < 100 IN dbsp1, c1 >= 100 AND c1 < 200 IN dbsp2,
   REMAINDER IN dbsp3;

To add another fragment in a new partition of dbspace dbsp2 to hold rows for c1 values between 200 and 299, use the following ALTER FRAGMENT statement:

ALTER FRAGMENT ON TABLE news 
   ADD PARTITION century3 (c1 >= 200 AND c1 < 300) IN dbsp2;

Any rows that were formerly in the remainder fragment but that fit the criteria (c1 >= 200 AND c1 < 300) move to the new century3 partition in dbspace dbsp2.