Adding an expression-based fragment

Adding a fragment expression to the fragmentation list of an expression-based distribution scheme can relocate records from existing fragments into the new fragment. When you insert a new fragment into the fragmentation list, the database server reevaluates all the data in the existing fragments that follow the new fragment. (The evalpos column value for any fragment in the sysfragments system catalog table indicates the ordinal position of that fragment within the fragmentation list.)

The next statement fragment 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 named fragment in dbspace dbsp2 to hold rows for column 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 fragment in dbspace dbsp2.

If the ALTER FRAGMENT ADD operation results in the redistribution of data rows while the automatic mode for updating distribution statistics is enabled, the database server drops the distribution statistics of the affected fragments, but the table statistics are not dropped. The next query on the table will cause the database server to recalculate the statistics for the same fragments.