Fragmenting by RANGE INTERVAL

You can use this storage distribution strategy to assign quantified values of the fragment key to nonoverlapping intervals within its range of numeric, or DATE, or DATETIME values.

Distributed storage based on RANGE INTERVAL fragmentation typically partitions the table into two types of fragments:
  • range fragments that you explicitly define in the FRAGMENT BY or PARTITION BY clause
  • interval fragments that the database server creates automatically during insert operations.

To fragment a table or index according to intervals within the range of a fragment key (also called a partitioning key), you must define the following parameters:

  • A fragment-key expression, based a single numeric, DATE, or DATETIME column.
  • At least one range expression. Rows with a fragment-key value within the specified range are stored in that fragment.
  • For each range expression, a list of at least one dbspace in which to store the corresponding fragment.

Fragments that you explicitly define by specifying a range expression are called range fragments. The syntax for RANGE INTERVAL fragmentation requires that at least one fragment be based on a range expression.

For system-generated fragments (called interval fragments) that the database server creates automatically to store rows in which the fragment-key value exceeds the upper limit for the current list of fragments, you can specify these additional parameters:

  • An interval size within the range of fragment-key values that each interval fragment will store.
  • A list of dbspaces in which to store interval fragments.

If you specify an interval size but the list of dbspaces is empty, interval fragments are stored in the same dbspaces that store the range fragments. If you specify no interval size, automatic creation of interval fragments is disabled. In that case, range fragments can store rows whose fragment-key values are within the specified ranges, but the table cannot store rows that have fragment-key values outside those ranges.

The CREATE INDEX statement also supports RANGE INTERVAL fragmentation strategies. If a table has an attached index defined with the same FRAGMENT BY RANGE syntax, corresponding index fragments (with the same names as the new table fragments) are similarly created automatically when rows outside the existing intervals are inserted.

A table or index fragmented by intervals of a range does not support a REMAINDER fragment, because if you define all the parameters that are identified above, the database server automatically creates new interval fragments to store inserted rows that have fragment-key values outside the range of any existing fragment.

For tables that have no NOT NULL constraint, you can define a NULL fragment by specifying VALUES IS NULL as the range expression.

The RANGE INTERVAL fragmentation strategy is useful when all possible fragment-key values in a growing table are not known, and the DBA does not want to preallocate fragments for data that is not yet there.

The following is an example of a table fragmented by range interval, using an integer column as the partitioning key:

CREATE TABLE employee (id INTEGER, name CHAR(32), basepay DECIMAL (10,2), 
                       varpay DECIMAL (10,2), dept CHAR(2), hiredate DATE)
       FRAGMENT BY RANGE (id) 
       INTERVAL (100) STORE IN (dbs1, dbs2, dbs3, dbs4)
             PARTITION p0 VALUES IS NULL IN dbs0,
             PARTITION p1 VALUES < 200 IN dbs1,
             PARTITION p2 VALUES < 400 IN dbs2;
In this table
  • the value of the interval size is 100,
  • the fragment key is the value of the employee.id column,
  • and the VALUES IS NULL keywords define p0 as the table fragment to store rows that have no id column value.

When employee ID exceeds 199, fragments are created automatically in intervals of 100, the specified interval size.

If a row is inserted with an employee ID of 405, a new interval fragment is created to accommodate the row. The new fragment holds rows with id column values in the range >= 400 AND < 500.

If a row is updated and the employee ID is modified to 821, the database server creates a new fragment to accommodate the new row. The fragment holds rows with id column values in the range >= 800 AND < 900.

The interval fragments are created in round-robin fashion in the dbspaces specified in the STORE IN clause. If this clause had been omitted, interval fragments would be created in the dbspaces that store the range fragments (dbspaces dbs0, dbs1 and dbs2 in the previous example). If a dbspace specified for the interval fragment is full or down, the database server skips that dbspace and selects the next one in the list.

Note that the range expressions for the interval fragments are non-overlapping, and there is no remainder fragment.

The fragment key for range interval fragmentation can reference only a single column. For example, the following specification is not valid:

FRAGMENT BY RANGE (basepay + varpay)

The fragment key can be a column expression, as in the following specification:

FRAGMENT BY RANGE ((ROUND(basepay))
No exclusive lock is required for fragment creation. The fragment-key expression must evaluate to a numeric, DATE, or DATETIME data type. For example, you can create a fragment for every month, or for every million customer records. The interval size specification (that follows the INTERVAL keyword) must be
  • a nonzero positive constant expression of a numeric data type (for numeric fragment keys),
  • or of an INTERVAL data type (for DATE or DATETIME fragment keys).

The ALTER FRAGMENT statement of SQL can apply a RANGE INTERVAL storage distribution to a nonfragmented table or index, as described in INIT Clause. That statement can also modify features of an existing RANGE INTERVAL strategy. For more information and examples, see MODIFY Clause and Examples of the MODIFY clause with interval fragments.