Examples of the MODIFY clause with interval fragments

Sections that follow illustrate syntax features of the MODIFY clause of the ALTER FRAGMENT statement, and restrictions on what the MODIFY clause can change, for tables that use range and interval fragments as their distribution strategy.

For similar examples of using the MODIFY clause with tables that are fragmented by list, see Examples of the MODIFY clause for list fragments.

Enabling or disabling range interval fragmentation

This statement disables range interval fragment creation:

ALTER FRAGMENT ON TABLE tab MODIFY INTERVAL DISABLED;

The following statement restores range interval fragment creation, undoing the effects of the previous example:

ALTER FRAGMENT ON TABLE tab MODIFY INTERVAL ENABLED;

The following statement disables range interval fragment creation, and also modifies the list of dbspaces in the STORE IN clause where new fragments will be stored (if a subsequent ALTER FRAGMENT MODIFY statement enables range interval fragment creation for table tab).

ALTER FRAGMENT ON TABLE tab MODIFY INTERVAL DISABLED 
   STORE IN (dbs4, dbs5);

Renaming fragments in range interval fragmentation

This statement renames two range interval fragments. No IN clause specifies new storage locations, so the new names replace the existing names for the two fragments:

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p1 TO PARTITION newp1, 
   PARTITION sys_p6 TO PARTITION newsys_p6;

The PARTITION keywords are required for range interval fragments. If you use the MODIFY clause to rename an existing fragment, the new name that you declare in the MODIFY clause cannot begin with the character string sys, which is reserved for system-defined fragments, but the example above successfully renames the system-defined fragment sys_p6.

Relocating a range or interval fragment

Suppose that the following table was created with range interval fragmentation and received two rows from insert operations:

CREATE TABLE tab2 (i INT, c CHAR(2))
   FRAGMENT BY RANGE (i) 
   INTERVAL (100) STORE IN (dbs1, dbs2, dbs3)
      PARTITION p0 VALUES < 100 IN dbs0,
      PARTITION p1 VALUES < 200 IN dbs1;
 
INSERT INTO tab2 VALUES (201, "AA");  
   -- creates a system-generated interval fragment sys_p2 
   -- with fragment expression >= 200 AND < 300 
   -- assume that this fragment is created in dbs1

INSERT INTO tab2 VALUES (601, "BB");  
   -- creates a system-generated interval fragment sys_p6 
   -- with fragment expression >= 600 AND < 700 
   ---assume that this fragment is created in dbs2

The following statement instructs the database server to move range fragment p1 from dbs1 to dbs2:

ALTER FRAGMENT ON TABLE tab2 MODIFY 
   PARTITION p1 TO PARTITION p1 IN dbs2;

The next example moves range fragment p1 from dbs1 to dbs2 and moves interval fragment sys_p6 from dbs2 to dbs3:

ALTER FRAGMENT ON TABLE tab2 MODIFY 
   PARTITION p1 TO PARTITION p1 IN dbs2, 
   PARTITION sys_p6 TO PARTITION sys_p6 IN dbs3;

Replacing the list of dbspaces that store new interval fragments

The following CREATE TABLE statement defines a range interval fragmentation strategy, in which

column i is the fragmentation key,
100 is the range interval size,
new fragments will be stored in dbspaces dbs1, dbs2, and dbs3,
the initial fragments p0 (in dbspace dbs0) and p1 (in dbspace dbs1) have transition values of 100 and 200 respectively.

CREATE TABLE tab (i INT, c CHAR(2))
   FRAGMENT BY RANGE (i) 
   INTERVAL (100) STORE IN (dbs1, dbs2, dbs3)
      PARTITION p0 VALUES < 100 IN dbs0,
      PARTITION p1 VALUES < 200 IN dbs1;

The following ALTER FRAGMENT statement replaces the STORE IN list (dbs1, dbs2, dbs3) with a new list (dbs4, dbs5).

ALTER FRAGMENT ON TABLE tab 
   MODIFY INTERVAL STORE IN (dbs4, dbs5);

In the example above, the MODIFY clause specifies that new fragments will be created alternately in dbs4 and dbs5. Any system-defined fragments (and the fragment p1) that were created in dbspaces of the original STORE IN list (dbs1, dbs2, dbs3) remain in those dbspaces. Existing and subsequently inserted rows whose fragmentation-key values are within the range intervals of those fragments continue to be stored in those fragments, but new interval fragments will be created, alternating in round-robin fashion, in the dbs4 and dbs5 dbspaces.

Consider that the following fragmented table:

CREATE TABLE mytab (col1 int)
   FRAGMENT BY RANGE (c1) INTERVAL (100) 
   STORE IN (dbs1, dbs2, dbs3, dbs4, dbs5)
      PARTITION p1 VALUES < 300 in dbs0;

This ALTER FRAGMENT statement replaces the list of dbspaces where new interval fragments will be stored:

ALTER FRAGMENT ON TABLE mytab MODIFY 
   STORE IN (dbs1, dbs6, dbs3, dbs4, dbs8);

The new list replaces dbs2 with dbs6 and replaces dbs5 with dbs8. If you want any of the dbspaces from the current STORE IN list to be available for new fragments, the MODIFY clause must also include them in the new list, which replaces the old list in the modified fragmentation scheme. In the example above, new range interval fragments will be created in the five dbspaces listed after the STORE IN keywords, but any existing fragments that were created in dbs2 and dbs5 continue to store rows whose data values match the fragmentation key ranges for those fragments.

You can modify the list of dbspaces in the STORE IN clause. The old list is replaced by the new list that you specify. Existing fragments in the old dbspace are not moved. Consider the following table:

You can move an existing fragment to another dbspace by changing the IN dbspace specification for the fragment:

CREATE TABLE tab (i INT, c CHAR(2))     
   FRAGMENT BY RANGE (i)      
   INTERVAL (100) STORE IN (dbs1, dbs2, dbs3) 
      PARTITION p0 VALUES < 100 IN dbs0,      
      PARTITION p1 VALUES < 200 IN dbs1;   

INSERT INTO tab VALUES (201, "AA");  
   -- creates interval fragment sys_p2 
   -- with fragment expression >= 200 AND < 300 
   -- (assume that this fragment is created in dbs1)
INSERT INTO tab VALUES (601, "BB");  
   -- creates interval fragment sys_p6 
   -- with fragment expression >= 600 AND < 700 
   -- (assume that this fragment is created in dbs2)

The next statement instructs the database server to moves fragment p1 from dbs1 to dbs2:

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p1 TO PARTITION p1 IN dbs2;

The next example moves range fragment p1 from dbs1 to dbs2, and moves interval fragment sys_p6 from dbs2 to dbs3;

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p1 TO PARTITION p1 IN dbs2, 
   PARTITION sys_p6 TO PARTITION sys_p6 IN dbs3;

You cannot, however, modify an expression for an interval fragment after the system has generated the fragment. Consider this table:

CREATE TABLE tab (i INT, c CHAR(2)) 
   FRAGMENT BY RANGE (i)      
   INTERVAL (100) STORE IN (dbs1, dbs2, dbs3) 
      PARTITION p0 VALUES < 100 IN dbs0,
      PARTITION p1 VALUES < 200 IN dbs1;   

INSERT INTO tab VALUES (201, "AA");  
   -- creates interval fragment sys_p2 
   -- with fragment expression >= 200 AND < 300 
INSERT INTO tab VALUES (601, "BB");  
   -- creates interval fragment sys_p6 
   -- with fragment expression >= 600 AND < 700

Now you cannot modify the fragment expression for sys_p2 or sys_p6. An error is returned if you try to do so.

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION sys_p6 TO PARTITION sys_p6 
      VALUES < 900 IN dbs2;

The above statement fails with an error.

Modifying the expression that defines a range fragment

Under some circumstances, you can use the MODIFY clause to change the expression that defines a range fragment; examples that follow illustrate various restrictions on changes that you can make to the expressions that define to range fragments. You cannot, however, modify an expression for an interval fragment after the system has generated that fragment. Consider this table:

CREATE TABLE tab (i INT, c CHAR(2))     
   FRAGMENT BY RANGE (i)      
   INTERVAL (100) STORE IN (dbs1, dbs2, dbs3) 
      PARTITION p0 VALUES < 100 IN dbs0,     
      PARTITION p1 VALUES < 200 IN dbs1;  

INSERT INTO tab VALUES (201, "AA");  
   -- creates interval fragment sys_p2 
   -- with fragment expression >= 200 AND < 300  
INSERT INTO tab VALUES (601, "BB");  
   -- creates interval fragment sys_p6 
    -- with fragment expression >= 600 AND < 700

Now you cannot modify the expressions for interval fragments sys_p2 or sys_p6. The database server returns an error if you try to do so.

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION sys_p6 TO PARTITION sys_p6 
      VALUES < 900 IN dbs2;

The above statement fails with an error.

You can modify an expression for the first intermediate range fragment, but the replacement expression cannot cross adjacent fragment boundaries. This operation can result in data movement. Here is an example;

CREATE TABLE tab (i INT, c CHAR(2))
   FRAGMENT BY RANGE (i) 
   INTERVAL (100) STORE IN (dbs1, dbs2, dbs3)
   PARTITION p0 VALUES < 100 IN dbs0,
   PARTITION p1 VALUES < 200 IN dbs1,
   PARTITION p2 VALUES < 300 IN dbs0; 

INSERT INTO tab VALUES (301, "AA");  
   -- creates interval fragment sys_p3 
   -- with fragment expression >= 300 AND < 400 
INSERT INTO tab VALUES (601, "BB");  
   -- creates interval fragment sys_p6 
   -- with fragment expression >= 600 AND < 700

All of the ALTER examples below are based on fragments of the table defined in the CREATE statement above. The following ALTER FRAGMENT statement modifies the expression for range fragment p0

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p0 TO PARTITION p0 
      VALUES < -50 IN dbs0;

The following modifies expression for fragment p0 and also moves the fragment from dbs0 to dbs5

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p0 TO PARTITION p0 
      VALUES < -50 IN dbs5;

The following statement successfully makes three changes to fragment p0:

modifies the fragment expression for p0,
modifies the fragment name to newp0,
and also moves the renamed fragment from dbs0 to dbs5.

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p0 TO PARTITION newp0 
      VALUES < -50 IN dbs5;

The next example fails with an error, however, because the new expression for fragment p0 crosses the boundary of the range of the next adjacent fragment p1

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p0 TO PARTITION p0 
      VALUES < 250 IN dbs0;

The following ALTER FRAGMENT example successfully modifies the expression for range fragment p1:

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p1 TO PARTITION p1 
      VALUES < 150 IN dbs1;

The following modification fails with an error, because the new expression for fragment p1 crosses the boundary of the previous adjacent fragment p0:

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p0 TO PARTITION p0 
      VALUES < 50 IN dbs0;

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

CREATE TABLE tab (i INT, c CHAR(2)) 
   FRAGMENT BY RANGE (i)      
      INTERVAL (100) STORE IN (dbs1, dbs2, dbs3)  
      PARTITION p0 VALUES IS NULL IN dbs0,    
      PARTITION p1 VALUES < 200 IN dbs1,     
      PARTITION p2 VALUES < 300 IN dbs0;    

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p0 TO PARTITION p0 
      VALUES < 100 IN dbs0;

As a result of the modification, the resultant table will have the following fragments

PARTITION p0 VALUES < 100 IN dbs0,  
      PARTITION p1 VALUES < 200 IN dbs1,
      PARTITION p2 VALUES < 300 IN dbs0

If the previous NULL fragment stored any rows (meaning rows in the table that have NULL value for column i), then those rows would not fit in any of the fragments in the new fragmentation scheme. The above ALTER FRAGMENT operation would therefore fail while moving rows.

Also note that the NULL fragment is always the first fragment in the table. Even if the user specifies the NULL fragment as the last fragment during CREATE TABLE or ALTER TABLE operations, it is rearranged as the first fragment in the table, with the smallest evalpos value in the fragment list. While modifying first and intermediate range fragments, the database server imposes the restriction that the new expression cannot cross adjacent fragment boundaries. So while modifying the NULL fragment, whatever new expression you specify cannot cross the boundary of the next range or interval fragment. Here is an example:

CREATE TABLE tab (i INT, c CHAR(2))     
   FRAGMENT BY RANGE (i)      
   INTERVAL (100) STORE IN (dbs1, dbs2, dbs3)
      PARTITION p0 VALUES IS NULL IN dbs0,  
      PARTITION p1 VALUES < 200 IN dbs1,  
      PARTITION p2 VALUES < 300 IN dbs0;

Suppose that the table does not have any rows in fragment p0. In this case, p0 can be modified to a non-NULL fragment.

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p0 TO PARTITION p0 
      VALUES < 250 IN dbs0;

Because the new expression for p0 (VALUES < 250) crosses the boundary for p1 (VALUES < 200), however, the example above returns an error.

The following ALTER FRAGMENT statement is possible:

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p0 TO PARTITION p0 VALUES < 150 IN dbs0;

You can modify expression for last range fragment (transition fragment) but you can only increase the transition value. There is no data movement in this operation.

CREATE TABLE tab (i INT, c CHAR(2))   
   FRAGMENT BY RANGE (i)    
   INTERVAL (100) STORE IN (dbs1, dbs2, dbs3) 
      PARTITION p0 VALUES < 100 IN dbs0,    
      PARTITION p1 VALUES < 200 IN dbs1,  
      PARTITION p2 VALUES < 300 IN dbs0;  
         -- last range fragment or transition fragment

The following modification returns an error, because it attempts to decrease the transition value (from 300 to 250):

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p2 TO PARTITION p2 
      VALUES < 250 IN dbs0;

The following statement modifies the fragment expression for p2 (the transition fragment). Because there are not yet any system-generated interval fragments, the new transition value need not align at the interval fragment boundary.

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p2 TO PARTITION p2 
      VALUES < 350 IN dbs0;

If there are no interval fragments between new and the old transition value, you can update the expression for the last range fragment to VALUES < new transition value. Here is an example:

CREATE TABLE tab (i INT, c CHAR(2))
   FRAGMENT BY RANGE (i)       
   INTERVAL (100) STORE IN (dbs1, dbs2, dbs3) 
       PARTITION p0 VALUES < 100 IN dbs0,    
       PARTITION p1 VALUES < 200 IN dbs1,   
       PARTITION p2 VALUES < 300 IN dbs0;  
         -- last range fragment is the "transition fragment"

INSERT INTO tab VALUES (601, "BB");  
   -- creates interval fragment sys_p6 
   -- with fragment expression >= 600 AND < 700 
   -- (assume that this fragment is created in dbs3)

The modified table now has these fragments:

Fragment: Expression and Fragment Type
p0: VALUES < 100 – range fragment
p1: VALUES < 200 – range fragment
p2: VALUES < 300 - last range fragment (or transition fragment)
sys_p6: VALUES >= 600 AND VALUES < 700 - interval fragment

During the change of transition value, the fragments are modified in a manner that does not cause any data movement.

The following statement modifies the fragment expression for p2, the transition fragment (or last range fragment).

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p2 TO PARTITION p2 VALUES < 500 IN dbs0;

The old transition value was 300 and the new transition value is 500. There are no system-generated interval fragments between these range fragments, and the first interval fragments starts at 600. This also means that there is no data rows between 300 and 500, so the expression of the transition fragment (the last range fragment) can be updated to VALUES < 500 without data movement. Because there are now interval fragments after the new transition value, the new transition value must align at an interval fragment boundary. In this case, the new transition value of 500 aligns with an interval fragment boundary. (The interval fragment need not exist.)

As a result of this modification, the evalpos value for subsequent interval fragments changes, and interval fragments are renamed to adhere to the format for system-generated fragment names. After this ALTER TABLE MODIFY operation, the resulting table has these fragments:

Fragment: Expression and Fragment Type
p0: VALUES < 100 – range fragment
p1: VALUES < 200 – range fragment
p2: VALUES < 500 – modified expression for transition fragment
sys_p4: VALUES >= 600 AND VALUES <700 – interval fragment

Here the modified expression is for fragment p2. which is the last range fragment. (This is also called the transition fragment, because any fragments to store larger values in the range of the fragment key will be system-generated interval fragments.) The system-generated interval fragment is renamed to sys_p4 because the evalpos value changes from 6 to 4 after the expression for the transition fragment changed.

The following modification fails with an error because there are interval fragments beyond the new transition value, and the new transition value does not align at an interval fragment boundary:

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p2 TO PARTITION p2 VALUES YY 550 IN dbs0;

The ranges of the possible interval fragments are 300 to 400, 400 to 500, 500 to 600, 600 to 700, and so on, but the new transition value of 550 is not at an interval fragment boundary, and so the database server issues an error.

If there are interval fragments between the new and old transition value, then the new transition value must align to the boundary of an interval fragment (but that interval fragment need not exist), unless the new transition value is beyond the last interval fragment. All interval fragments between the new and old transition values are converted to range fragments, and their expressions are modified to match the range fragment expressions. The expression for the last interval fragment that was converted to a range fragment is updated to VALUES < new transition value.

This behavior is illustrated by the following example

CREATE TABLE tab (i INT, c CHAR(2))
   FRAGMENT BY RANGE (i) 
   INTERVAL (100) STORE IN (dbs1, dbs2, dbs3)
      PARTITION p0 VALUES < 100 IN dbs0,
      PARTITION p1 VALUES < 200 IN dbs1,
      PARTITION p2 VALUES < 300 IN dbs0;  
        -- last range fragment or transition fragment

INSERT INTO tab VALUES (301, "AA");  
   -- creates interval fragment sys_p3 
   -- with fragment expression >= 300 AND < 400 
   -- (assume this fragment is created in dbs1)
INSERT INTO tab VALUES (601, "BB");  
   -- creates interval fragment sys_p6 
   -- with fragment expression >= 600 AND < 700 
   -- (assume this fragment is created in dbs3)

After the two INSERT operations, the table would have these range and interval fragments:

Fragment: Expression and Fragment Type
p0: VALUES < 100 – range fragment
p1: VALUES < 200 – range fragment
p2: VALUES < 300 – range fragment
sys_p3: VALUES >= 300 AND VALUES <400 – interval fragment
sys_p4: VALUES >= 600 AND VALUES <700 – interval fragment

The ALTER FRAGMENT examples that follow are based on this table.

The following example modifies the expression for fragment p2 (the transition fragment).

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p2 TO PARTITION p2 VALUES < 500 IN dbs0;

Because there is an interval fragment (sys_p3) between the old and new transition value, that fragment is converted to range fragment (whose expression becomes VALUES < 400).

And because there are interval fragments beyond the new transition value (for fragment sys_p6), the new transition value must align at an interval fragment boundary, which it does. The possible interval fragments must be an integer multiple of the range interval size (including 400 to 500, 500 to 600, 700 to 800, and so on. The new transition value is 500, which is at an interval fragment boundary. It is also efficient to avoid move data during the transition fragment modification, and to avoid creating any fragments. This can be made possible by converting fragment sys_p3 to the new transition fragment, updating its expression to < 500, and renaming it to the name of the old transition fragment.

The resulting table has the following fragments:

Fragment: Expression and Fragment Type
p0: VALUES < 100 – range fragment
p1: VALUES < 200 – range fragment
sys_p2rg: VALUES < 300 – range fragment (This was the old transition fragment, now renamed sys_p2rg in the system generated format sys_pevalposrg.)
p2: VALUES <500 - range fragment (This was previously interval fragment sys_p3. Its expression, modified to a range expression. now defines the new transition fragment)
sys_p5: VALUES >= 600 AND VALUES <700 – interval fragment (renamed to sys_5 as its evalpos value changes from 6 to 5 after the transition fragment change)

The following modification of transition fragment p2 returns an error:

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p2 TO PARTITION p2 VALUES < 550 IN dbs0;

The error is issued because there is an interval fragment sys_p6 beyond the new transition value, and the new transition value is not aligned at an interval fragment boundary.

The next example modifies the expression for fragment p2, which is a transition fragment:

ALTER FRAGMENT ON TABLE tab MODIFY 
   PARTITION p2 TO PARTITION p2 VALUES < 750 IN dbs0;

Because there are no interval fragments beyond the new transition value, it need not align to an interval fragment boundary.

The resulting table has the following fragments:

Fragment: Expression and Fragment Type
p0: VALUES < 100 – range fragment
p1: VALUES < 200 – range fragment
sys_p2rg: VALUES < 300 – range fragment (This was the old transition fragment, now renamed sys_p2rg in the system generated format sys_pevalposrg.)
sys_p3rg: < 400 – range fragment (This was previously interval fragment sys_p3 before its expression was modified to a range expression.)
p2: VALUES <750 - range fragment (Previously interval fragment sys_p6 before its expression was modified to a range expression. This becomes the new transition fragment.)