Examples of the MODIFY clause for list fragments

You can use the MODIFY clause to change fragments of a table or index that is fragmented by list, including these modifications:

Change the names of existing list fragments
Move the storage location of an existing list fragment to another dbspace
Modify the expression list for one or more list fragments.

The following ALTER FRAGMENT ON TABLE statement modifies the name, the list of fragment expressions, and the storage location for a fragment of a table that is partitioned by list:

ALTER FRAGMENT ON TABLE T2 MODIFY
   PARTITION part1 TO PARTITION part11 
      VALUES ('CA', 'OR', 'TX') IN dbs1;

This changes the partition name from part1 to part11, adds the value 'TX' to the list of expressions for that fragment, and moves the renamed partition into the dbspace dbs1.

Examples that follow illustrate these and other uses of the MODIFY clause with list fragmentation schemes, and also illustrate MODIFY operations that fail because of logical restrictions on list fragmentation.

Suppose that this CREATE TABLE statement has defined table myTable with the following schema and with a list fragmentation strategy:

CREATE TABLE myTable (i int, c char(2)) 
    FRAGMENT BY LIST (c)
        PARTITION p1 VALUES ("AB", "CD") IN dbs1,
        PARTITION p2 VALUES ("PQ", "RS") IN dbs2,
        PARTITION p3 REMAINDER IN dbs3;

The next ALTER FRAGMENT statement modifies the storage distribution strategy for the p2 fragment:

ALTER FRAGMENT ON TABLE myTable MODIFY 
   PARTITION p2 TO PARTITION newp2 
      VALUES (NULL) IN dbs5;

The statement above has these effects on the definition of the fragment and its storage distribution:

redefines the fragment expression for fragment p2 to make it a NULL fragment,
changes the fragment name to newp2,
moves the storage location of that fragment from dbs2 to dbs5,
and moves any existing data rows that had been stored in fragment p2 to the remainder fragment p3, because the fragment key values ("PQ" and "RS") in column c of those rows do not match the new NULL expression.

If the automatic mode for updating distribution statistics is enabled, an ALTER FRAGMENT . . . MODIFY statement that results in data redistribution causes the fragment-level statistics for the affected fragments to be dropped. Table-level statistics, however, are not dropped. Because no fragment-level statistics exist for the affected fragments, the next explicit or automatic UPDATE STATISTICS operation on the table will rebuild the fragment-level distributions, and store the results in the system catalog.

The ALTER FRAGMENT examples that follow specify modifications to fragments of the tab table that this CREATE TABLE statement defines with a list fragmentation scheme:

CREATE TABLE tab (i int, c char(2)) 
    FRAGMENT BY LIST (c)
        PARTITION p1 VALUES ("AB", "CD") IN dbs1,
        PARTITION p2 VALUES ("PQ", "RS") IN dbs2,
        PARTITION p3 VALUES (NULL) IN dbs3,
        PARTITION p4 REMAINDER IN dbs4;

The following modifies fragment expression for fragment p1

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p1 TO PARTITION p1 
      VALUES ("AB", "CD", "EF") IN dbs1;

The following statement modifies the fragment expression for fragment p3:

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p3 TO PARTITION p3 
      VALUES ("XX", "YY", "ZZ") IN dbs3;

If for any reason, as result of the ALTER FRAGMENT ON TABLE MODIFY operation, any rows cannot be moved to the new fragments, an error is returned, as in the following example:

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p3 TO PARTITION p3 
      VALUES ("XX", "YY", "ZZ") IN dbs3;

As a result of the modification, the resulting storage distribution scheme for the tab table will have the following fragments:

    PARTITION p1 VALUES ("AB", "CD")       IN dbs1,
    PARTITION p2 VALUES ("PQ", "RS")       IN dbs2,
    PARTITION p3 VALUES ("XX", "YY", "ZZ") IN dbs2

If the previous remainder fragment p3 had a row with value "AA" in column c, then that row does not fit in any of the fragments in the new fragmentation scheme. The ALTER FRAGMENT statement above would therefore fail with an error while attempting to move rows from the remainder fragment.

The next three examples illustrate modifications to the same table fragmentation scheme that will fail because of overlaps.

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p2 TO PARTITION p2 VALUES (NULL) IN dbs2;

Because the ALTER FRAGMENT statement above attempts to change fragment p2 into a duplicate NULL fragment, the statement fails with an error, because the NULL fragment p3 already exists.

The following modification of the same table attempts to modify fragment p2 into a duplicate remainder fragment:

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p2 TO PARTITION p2 REMAINDER IN dbs2;

The statement above fails with an error, because the existing fragment p4 is already defined as a remainder fragment.

The following modification creates a duplicate expression-list value "RS" in two of the fragments:

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p1 TO PARTITION p1 
      VALUES ("AB", "CD", "RS") IN dbs1;

Because list value "RS" is already defined in the expression list for fragment p2, the statement above fails with an error.

For an example of using the MODIFY option to the ALTER FRAGMENT ON INDEX statement, see Examples of ALTER FRAGMENT ON INDEX statements.