Restrictions on the MODIFY clause for range interval fragments

The MODIFY clause of the ALTER FRAGMENT statement cannot change the interval value, nor the fragment key. To change either of these elements of the range interval storage distribution scheme, you must use the INIT option of the ALTER FRAGMENT statement.

The MODIFY clause cannot change the value of the range expression of a fragment if any of the following are true:
  • The fragment is the last fragment, and the new value is smaller than the current value.
  • The new value overlaps the boundary of an existing fragment.
  • The fragment is a system-generated interval fragment.
You can modify the value of a user-defined range fragment, but the new value cannot cross adjacent fragment boundaries, and the database server must be able to accomplish any data movement that the new range expression implies.

The MODIFY clause can change the list of storage spaces where an existing fragment is stored, and it can also change the list of storage spaces where new system-generated interval fragments will be stored, but the same MODIFY clause cannot accomplish both tasks. To change both lists, you must issue two separate ALTER FRAGMENT . . . MODIFY statements.

Similarly, a MODIFY clause that enables or disables the current range interval distribution scheme cannot also move an existing range interval fragment to a different dbspace, or create a new user-defined fragment. Separate ALTER FRAGMENT . . . MODIFY statements are required for each of these tasks.

For range fragments of tables and indexes fragmented by interval, you can modify the fragment expression for the first and for intermediate range fragments. Any overlaps are resolved by moving data, so that fragment key values of the rows stored in the redefined range fragments are non-overlapping. For the last range fragment, however, you can modify the transition value in its fragment expression only if the new range expression satisfies all of the following conditions:
  • It does not partially or completely match any existing interval fragment expression.
  • It will not partially match any future interval fragment expressions that the system can generate automatically.
  • Any gap that the new transition value leaves between fragments must be an integer multiple of the intvl_expr interval value.

You cannot define a remainder fragment for a table that is fragmented by range interval.

If you use the MODIFY clause to rename an existing fragment, the new name cannot begin with the characters sys_p.

Range, interval, and transition fragments

For objects that use a range interval storage distribution strategy, it is useful to distinguish among three types of fragments:

  • A range fragment is a fragment whose name, fragment-key expression, and storage location are defined explicitly in the Interval Fragment clause within the table or index definition. Range interval fragmentation requires that at least one range fragment be defined.
  • An interval fragment is a fragment whose name, fragment-key expression, and storage location are defined automatically by the database server when an insert or load operation attempts to store a row whose fragment-key value is false for the fragment-key expression of every existing fragment.
  • The range fragment whose upper limit in its VALUES clause is larger than for the fragment-key expression for any other range fragment is called the transition fragment. The upper limit specified in the VALUES clause of the transition fragment is called the transition value for the table. If no interval fragments have been created for the object, inserting a row whose fragment-key value exceeds that transition value requires the database server to create a new interval fragment.

Operations that the MODIFY clause of the ALTER FRAGMENT statement can perform on transition fragments are more restricted than MODIFY operations on other range and interval fragments.

The ALTER FRAGMENT MODIFY statement cannot change the range expression that defines a transition fragment unless you also include the MODIFY TRANSITION keywords.

The database server cannot create interval fragments unless the Interval Fragment clause within the table or index definition defined a range interval fragment key, and the fragmentation scheme is not currently disabled by the ALTER FRAGMENT . . . MODIFY INTERVAL DISABLE statement.

Restrictions on modifying rolling window tables

The Rolling Window clause of the ALTER FRAGMENT MODIFY INTERVAL statement cannot define a purging policy on a table that has any of the following attributes:

  • The table has a ROWID shadow column.
  • Another table has a foreign key constraint that references a PRIMARY KEY in the table.