Fragmentation by INTERVAL

An interval fragmentation strategy partitions data into fragments based on an interval value of the fragment key. The interval value must be a column expression that references a single column of a numeric, DATE, or DATETIME data type.

This type of distribution scheme is sometimes called a range interval distribution because:

The RANGE and INTERVAL keywords are required in the DDL syntax that defines this strategy.
The initial user-defined fragments are called range fragmentsto distinguish these fragments from system-defined fragments. The database server creates system-defined fragments automatically when a row is inserted whose fragment key value does not match the expression that defines any existing fragment.

The INTERVAL distribution strategy is useful when all possible fragment key values in a growing table are not known, and the DBA does not want to allocate fragments for data rows that are not yet loaded. For example, by using a DATE column as a fragment key could define a fragment for every month, or a BIGSERIAL column as a fragment key could define a fragment for every million customer records. The automatic creation of interval fragments avoids the need for a REMAINDER fragment (with its associated fragment-elimination difficulties) and can also reduce the maintenance workload of the DBA.

Defining an interval distribution strategy

The definition of an interval distribution scheme can include several required or optional parameters:

Fragment key: This must specify a column expression referencing a single numeric, DATE, or DATETIME column of the table.
Interval value expression: This constant expression defines an interval size within the range of fragment key values for system-generated interval fragments.
Storage location for interval fragments: This is a list of dbspaces where interval fragments will be stored.
Range fragment list: You must declare the name and define the fragment expression and the storage location for at least one range fragment.

The syntax for defining these parameters of a range interval distribution has this general form:

   FRAGMENT BY RANGE (column_expr) 
      INTERVAL (interval_size) STORE IN (dbspace_list)
         PARTITION partition VALUES < upper_bound IN dbspace,
                  . . . 
         PARTITION partition VALUES < upper_bound IN dbspace,
         PARTITION partition VALUES IS NULL IN dbspace

In this template, the syntax tokens that are not keywords specify the following parameters of the storage distribution scheme:

RANGE(column_expr): This column expression, referencing a single column and delimited by parentheses, defines the fragment key. This clause is required.
INTERVAL(interval_size): This interval value expression, delimited by parentheses, defines the interval size (within the range of values of the fragment key) for system-generated interval fragments. This clause is required.
STORE IN(dbspace_list): This is a list of dbspaces where interval fragments will be stored. If you specify more than one dbspace, the database server creates successive new interval fragments in these dbspaces, in round robin fashion. This clause is optional.
If you omit this clause, the database server stores interval fragments in the dbspace that stores the range fragment. (If you define two or more range fragments and store them in different dbspaces, the database server stores each new interval fragment in one of these dbspaces, assigning successive interval fragments to these dbspaces in round robin fashion.)
PARTITION partition: This declares the name of a range or NULL fragment. You must define at least one range fragment. The NULL fragment is optional, but no more than one NULL fragment can be defined.
If you define more than one fragment, their names must conform to the rules for SQL identifiers, and must be unique among the fragments of the same table or index.
VALUES < upper_bound: This defines the fragment expression. Unlike list fragments, which can be defined in an arbitrary order, if you define more than one range fragment, their expressions must be defined in ascending order of the upper_bound. This clause is required.
The last range fragment that you define (which can be the first, if you define only one), is called the transition fragment, and its upper bound is called the transition value for the fragmented object. Any inserted rows with a larger fragment key value must be stored in an interval fragment.
VALUES IS NULL: This is the fragment expression to define the NULL fragment. Whether you define a NULL fragment is optional. The NULL fragment is not a range fragment, because NULL indicates the absence of a fragment key value. The database server issues an exception if a DML operation attempts to insert a row that has no fragment key value into a fragmented table for which no NULL fragment is defined.
If you define a NULL fragment, it can be listed in any position within the PARTITION specifications. The database server, rather than the sequence in which you declare the fragments, internally determines the order of each fragment within the fragment list of a table or index that uses an interval fragmentation scheme. The NULL fragment, if it exists, is always the first on this list, as indicated by its sysfragments.evalpos value in the system catalog.

When a row is inserted whose fragment key value is outside the range of any existing range or interval fragments, the database server will automatically create a new interval fragment based on the interval_size value and the transition value, without DBA intervention.

This kind of fragmentation strategy is useful when all possible fragment key values in a growing table are not known and the DBA does not want to allocate fragments for data that is not yet loaded.