Using the MODIFY INTERVAL TRANSITION option

You can use this option to increase the transition value of the last range fragment of a table that has a range-interval fragmentation scheme. The transition value cannot be decreased by using this MODIFY INTERVAL TRANSITION option to the ALTER FRAGMENT statement.

You cannot use the PARTITION partition VALUES syntax of the MODIFY option to modify the range expression for the last range fragment (also called the transition fragment) of a table that uses a range-interval storage distribution scheme. The transition value, however, which is the upper limit in the range expression, can be increased by using the MODIFY INTERVAL TRANSITION TO keywords to specify the new upper limit. There is no data movement when the transition value is changed.

To decrease the transition value (by resetting the upper limit on the range of the transition fragment), you must perform an ALTER FRAGMENT INIT operation to redefine the range-interval distributed storage scheme for the table.

Automatic fragment renaming when the transition value increases

ALTER FRAGMENT statements that specify the MODIFY INTERVAL TRANSITION option can cause existing fragments to be renamed:

If there are no interval fragments between the new and old transition values, but interval fragments already exist above the new transition value, the digit that terminates the system-generated interval fragment name is reduced by the number of interval fragment boundaries occupied by the difference between the new and old transition values.
For example, if the interval value expression defining an interval size evaluates to 20, and the difference between the old transition value and the new transition value is 60, then an interval fragment whose name is sys_p7 is renamed to sys_p4, because the quotient is (60/20) = 3.
If interval fragments exist between the new and old transition values, the characters rg are appended to their names to indicate that they have become range fragments, because the upper limit of their fragment expression is no longer greater than the transition value for the table.
For example, if the transition value of a table were increased to match the upper VALUES limit of its interval fragment sys_p5, that fragment would be changed to a range fragment, and renamed sys_p5rg. (It would also become the transition fragment.) If another interval fragment called sys_p4 also had a smaller VALUES upper limit in its fragment expression, that fragment would also become a range fragment, and would be renamed sys_p4rg.

During a fragment renaming operation, an exclusive lock is placed on the fragment while the sysfragments system catalog table is being updated

with new fragment-identifier values in the partition column,
and with new integer values in the evalpos column for any interval fragments or rolling interval fragments whose ordinal positions in the fragment list changed during the current ALTER FRAGMENT MODIFY operation.

In the cases listed above, some fragments are renamed to ensure that every fragment name in the fragment list is unique, and to maintain the correlation between system-generated names for interval fragments and the corresponding sysfragments.evalpos value for those fragments in the system catalog. (See also the section Automatic renaming of interval fragment identifiers.)

Several of the ALTER FRAGMENT examples that follow illustrate this fragment-renaming behavior.

Example of ALTER FRAGMENT MODIFY INTERVAL TRANSITION

The following statements define a fragmented tabtrans table that uses a range-interval storage distribution scheme, with the integer column i the fragment key, and an interval value of 100. The transition fragment p2 has a transition value of 300, meaning that the database server will define a new interval fragment during any operation on the table to store a new row with a fragment key value of 300 or greater.

CREATE TABLE tabtrans (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 (also 
                                          -- called transition fragment)

Examples that follow are based on this tabtrans table.

The following ALTER FRAGMENT statement attempts to increase the transition fragment value from 300 to 250:

ALTER FRAGMENT ON TABLE tabtrans 
   MODIFY INTERVAL TRANSITION TO 250;

This statement fails, because it attempts to reduce the transition value. If the design goal is to keep the current interval value of 100, but for the new transition value to become 250, an ALTER FRAGMENT INIT operation is required to redefine the range fragments. To keep the range-fragment boundaries aligned, the new upper limit for the range fragment immediately preceding the transition fragment must be 150. In the new distributes storage scheme, if a row with a fragment-key value greater than 250 is inserted into the table, the database server generates a new interval fragment with a range of 100, and with an integer value of the form 50 (modulo 100) as the upper limit.

If there are no interval fragments between the new and old transition values, the database server updates the expression for the last range fragment to VALUES < new, where new is the new transition value:

INSERT INTO tabtrans VALUES (601, "BB");  -- creates interval fragment sys_p6 
                                     -- with fragment expression >= 600 AND < 700

The fragment list and the fragment expressions for the tabtrans table become as follows

  p0        VALUES < 100                   - range fragment
  p1        VALUES < 200                   - range fragment
  p2        VALUES < 300                   - last range (or transition) fragment
  sys_p6    VALUES >= 600 AND VALUES < 700 - interval fragment

Here the system-generated name of the new interval fragment is sys_p6 because 6 is the sysfragments.evalpos value for the new fragment in the system catalog. The evalpos values 7 and 5 are reserved for (not yet created) interval fragments to store rows whose fragment key values match the fragment expressions VALUES >= 300 AND VALUES < 400 and VALUES >= 400 AND VALUES < 500, based on the current transition value of the table and on the INTERVAL (100) specification in the FRAGMENT BY clause that defined the fragmentation scheme of the table.

During a change of transition value, the fragments are modified to not cause any data movement. The following statement successfully modifies the transition value to 500.

ALTER FRAGMENT ON TABLE tabtrans 
   MODIFY INTERVAL TRANSITION TO 500;

The old transition value is 300 and the new transition value is 500, with no interval fragments in between. The first interval fragments starts at 600. This also means that there is no data between 300 and 500. So the expression of the last range fragment (the transition fragment) can be updated to VALUES < 500 without data movement. Because there are interval fragments after the new transition value, the new transition value must align at an interval fragment boundary. In the above case, the new transition value 500 aligns at an interval fragment boundary (whether or not the fragment currently exists). As result of modification, the evalpos value in the system catalog for interval fragments changes, and the interval fragments are renamed to adhere to the sys_pevalpos naming format.

The resulting modified table has the following fragments:

  p0     VALUES < 100  -- range fragment
  p1     VALUES < 200  -- range fragment
  p2     VALUES < 500  -- last range fragment (= transition fragment
                       -- with its expression modified)
  sys_p4 VALUES >= 600 AND VALUES < 700 - interval fragment (renamed
                       -- to sys_p4 as evalpos changes from 6 to 4 
                       -- after the transition fragment change)

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 INTERVAL TRANSITION TO 550;

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

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

Here is another example of INSERT operations that result in new interval fragments:

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
INSERT INTO tab 
   VALUES (601, "BB"); -- creates interval fragment sys_p6 
                       -- with fragment expression >= 600 AND < 700

The fragment list for the table now consists of these fragments:

  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_p6    VALUES >= 600 AND VALUES < 700 -- interval fragment

The ALTER FRAGMENT examples that follow are based on the above statements.

The following statement increases the transition value from 300 to 500:

   ALTER FRAGMENT ON TABLE tab MODIFY INTERVAL TRANSITION TO 500;

Because there is an interval fragment (sys_p3) between the old and new transition values, that fragment is converted to a range fragment (expression becomes < 400). Because there is also an interval fragment (sys_p6) beyond the new transition value, the new transition value must align at an interval fragment boundary, which as an integer multiple of the INTERVAL(100) specification, it does. Here the possible interval fragments are 300 to 400, 400 to 500, 500 to 600, 600 to 700 and so on. And the new transition value of 500 is at an interval fragment boundary (whose fragment need not exist). We also do not want to move data during the transition fragment modification or create any fragments. This can be accomplished by converting fragment sys_p3 to the new transition fragment, updating its expression to < 500 (because it is now a range fragment), and renaming it.

The resulting fragment list for the table consists of these fragments:

  p0          VALUES < 100  -- range fragment
  p1          VALUES < 200  -- range fragment
  p2          VALUES < 300  -- range fragment (was the old transition fragment)
  sys_p3rg    VALUES < 500  -- range fragment (was previously interval 
                            -- fragment sys_p3. Its expression was modified to a
                            -- range expression. Its name was changed to a
                            -- system-generated name in format sys_p<evalpos>rq )
                            -- becomes the new transition fragment
  sys_p5      VALUES >= 600 AND VALUES < 700
                            -- interval fragment (renamed to sys_5 brcause the 
                            -- evalpos value changes from 6 to 5 after the 
                            -- transition fragment change.)

The following attempted modification of the transition value returns an error:

ALTER FRAGMENT ON TABLE tab 
   MODIFY INTERVAL TRANSITION TO 550;

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

The next example increases the transition value from 500 to 700:

ALTER FRAGMENT ON TABLE tab 
   MODIFY INTERVAL TRANSITION TO 700;

The resulting fragment list for the table includes the following fragments:

  p0         VALUES < 100 -- range fragment
  p1         VALUES < 200 -- range fragment
  p2         VALUES < 300 -- range fragment (was the old transition fragment)
  sys_p3rg   VALUES < 400 -- range fragment (was previously interval fragment 
                          -- sys_p3, and its expression changed to a range 
	                           expression.
                          -- The fragment has been renamed to system-generated name 
                          -- in the format sys_p<evalpos>rg ).
  sys_p6rg   VALUES < 700 -- range fragment (was previously the interval 
                          -- fragment sys_p6. Its expression was modified to a
                          -- range expression and its name replaced by a system- 
                          -- generated name in the format sys_p<evalpos>rg )
                          -- becomes the new transition fragment.

The following example increases the transition value from 700 to 750:

ALTER FRAGMENT ON TABLE tab MODIFY INTERVAL TRANSITION TO 750;

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

The resulting table includes the following fragments:

  p0         VALUES < 100  -- range fragment
  p1         VALUES < 200  -- range fragment
  p2         VALUES < 300  -- range fragment (was the old transition fragment)
  sys_p3rg   VALUES < 400  -- range fragment (was previously interval  
                           -- fragment sys_p3. expression modified to a 
                           -- range expression. Fragment was renamed to a system 
                           -- generated name in the format sys_p<evalpos>rg) 
  sys_p6rg   VALUES < 750  -- range fragment (was previously the interval 
                           -- fragment sys_p6. Its expression was modified to a
                           -- range expression, and the fragment was renamed to a
                           -- system-generated name in format sys_p<evalpos>rg) 
                           -- becomes the new transition fragment

If you wish to avoid having existing fragments automatically renamed during ALTER FRAGMENT MODIFY INTERVAL TRANSITION operations, you can first use the ALTER FRAGMENT MODIFY statement to rename with user-defined names the interval fragments whose system-generated names might otherwise be changed by the ALTER FRAGMENT MODIFY INTERVAL TRANSITION statement. The database server renames only system-generated interval fragment names (to avoid non-unique fragment names resulting when new interval fragments are created).