Fragment by list and interval
Fragmenting by list defines fragments that are each based upon a list of discrete values of the fragment key.
You can use this fragmentation strategy when the values of the fragment key are categories on a nominal scale that has no quantified order within the set of categories. Fragmenting by list is useful when a table contains a finite set of values for the fragment key, and queries on the table have an equality predicate on the fragment key. For example, you can fragment data geographically, based on a list of the states or provinces within a country. The rows that are stored in each fragment can be restricted to a single fragment key value, or to a list of values representing some logical subset of fragment key values, provided that no fragment key value is shared by two or more fragments.
Fragmenting by list also helps to logically segregate data, as shown in Listing 5.
Listing 5. Example of a list fragmented table
CREATE TABLE customer (id SERIAL, fname CHAR(32), lname CHAR(32), state CHAR(2), phone CHAR(12)) FRAGMENT BY LIST (state) PARTITION p0 VALUES ("KS", "IL") IN dbs0, PARTITION p1 VALUES ("CA", "OR") IN dbs1, PARTITION p2 VALUES ("NY", "MN") IN dbs2, PARTITION p3 VALUES (NULL) IN dbs3, PARTITION p4 REMAINDER IN dbs3;
In the previous example, the table is fragmented on column state, which is called the fragment key or partitioning key. The fragment key can be a column expression, as shown in the following example.
FRAGMENT BY LIST (SUBSTR(phone, 1, 3))
The fragment key expression can have multiple columns, as in the following example.
FRAGMENT BY LIST (fname[1,1] || lname[1,1])
The fragments must be non-overlapping, which means that duplicates are not allowed in the list values. For example, the following expression lists are not valid for fragments of the same table or index, because their KS expressions overlap.
PARTITION p0 VALUES ("KS", "IL") IN dbs0, PARTITION p1 VALUES ("CA", "KS") IN dbs1, PARTITION p0 VALUES ("KS", "IL", "KS") IN dbs0,
The list values must be constant literals. For example, the identifier or variable name is not allowed in the following list.
PARTITION p0 VALUES (name, "KS", "IL") IN dbs0,
A null fragment is a fragment that contains rows with NULL values for the fragment key column. You cannot mix NULL and other list values in a single fragment definition. For example, the following is not allowed.
PARTITION p0 VALUES ("KS", "IL", NULL) IN dbs0,
A remainder fragment is a fragment that stores the rows whose fragment key value does not match any expression in the expression lists of the explicitly defined fragments. If you define a remainder fragment, it must be the last fragment listed in the FRAGMENT BY or PARTITION BY clause that defines the list fragmentation strategy.
Fragmenting by list supports the following features.
- Both a table and its indexes can be fragmented by list.
- The fragment key can be a column expression based on a single column or on multiple columns.
- The list can optionally include a remainder fragment.
- The list can optionally include a NULL fragment that stores only NULL values.
Fragmenting by list must satisfy the following requirements.
- The list cannot include duplicates.
- The list that includes NULL (or IS NULL) cannot include any other value.
- The fragment key must be based on a single row.
- The fragment key must be a column expression. This can be based on a single column or on multiple columns.
- An error is issued if a row matches no value on the list, and no remainder fragment is defined.
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. You can use this fragmentation strategy to assign quantified values of the fragment key to non-overlapping intervals within its range of numeric or time values. 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.
To fragment a table or index according to intervals within the range of its index key, you must define the following parameters.
- A fragmentation key, based on a numeric, DATE, or DATETIME column.
- An interval size, specifying the range of fragment key values assigned to a single fragment.
- One or more dbspaces in which to store fragments for values outside the initial fragment key range.
- One or more named fragments defined by fragment key range expressions and stored in dbspaces.
Fragmentation by intervals of a range does not support a remainder fragment, because after the Interval Fragment clause defines the required parameters and at least one initial fragment, the database server can automatically define new fragments of the same interval size to store rows whose interval key values are outside the range of the initial fragments. The fragments that the server creates are stored in a round-robin fashion in dbspaces that you specify after the STORE IN keywords.
For example, you can create a fragment for every month or for every million customer records. Interval fragmentation requires that at least one fragment be based on a range expression. When rows are inserted with fragment-key values that do not fit in any of the range fragments that you defined, the database server automatically creates one or more new interval fragments, based on the same interval size within the fragment-key range, to store the new rows.
The CREATE INDEX statement supports the same 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.
The fragment by 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 pre-allocate fragments for data that is not yet there, as shown in Listing 6.
Listing 6. Example of an interval fragmented table
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;
Here 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 with no id column value.
When employee ID exceeds 199, fragments are created automatically in intervals of 100, which is the interval value. The automatically created fragments are called interval fragments.
If a row is inserted with an employee ID of 405, a new fragment is created to accommodate the row. The new fragment holds values >= 400 AND < 500.
If a row is updated and the employee ID is modified to 821, a new fragment is created to accommodate the new row. The fragment holds values that are >= 800 AND < 900.
The initial range expressions for interval fragmentation are non-overlapping and there are no remainder fragments.
The fragment or partitioning key for interval fragmentation can reference only a single column. For example, the following is not allowed.
FRAGMENT BY RANGE (basepay + varpay)
The fragment key can be a column expression, as in the following example.
FRAGMENT BY RANGE ((ROUND(basepay))
The fragment key expression must evaluate to a numeric, DATE, or DATETIME data type.
No exclusive lock is required for fragment creation. The interval value must be 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 interval fragments are created in round-robin fashion in the dbspaces specified in the STORE IN clause. The STORE IN clause is optional and, if omitted, interval fragments are created in the dbspaces of the initial fragments (dspaces dbs0, dbs1 and dbs2 in the previous example). If the dbspace selected for the interval fragment is full or down, the system skips that dbspace and selects the next one in the list.