Distribution schemes
After you decide whether to fragment table rows, index keys, or both, and you decide how the rows and keys should be distributed over fragments, you can decide on a scheme to implement this distribution. Informix® supports random distribution among fragments and value-based distribution among fragments.
Random distribution among fragments
- Round-robin fragmentation
- This type of fragmentation places rows one after another in fragments,
rotating through the series of fragments to distribute the rows evenly.
For smart large objects, you can specify multiple sbspaces in the PUT clause of the CREATE TABLE or ALTER TABLE statement to distribute smart large objects in a round-robin distribution scheme so that the number of smart large objects in each space is approximately equal.
Value-based distribution among fragments
- Expression-based fragmentation
- This type of fragmentation puts rows that contain specified values
in the same fragment. You specify a fragmentation expression that
defines criteria for assigning a set of rows to each fragment, either
as a range rule or some arbitrary rule.
You can specify a remainder fragment that holds all rows that do not match the criteria for any other fragment, although a remainder fragment reduces the efficiency of the expression-based distribution scheme.
- List-based fragmentation
- This type of fragmentation puts rows that contain specified values
that match one of the specified values in a list of discrete values
in the same fragment. For each fragment, you specify a list of one
or more constant expressions as fragment expressions that correspond
to one or more columns in the table. The column or set of columns
from which the fragment expressions are calculated is called
the fragment key.
You can optionally specify a remainder fragment that holds all rows that do not match the criteria for any other fragment. You can also optionally specify a NULL fragment that stores rows with missing data in the fragment key columns (because its fragment expression is NULL or IS NULL).
The most important difference between fragmentation by list and fragmentation by expression is that every value in the list for each fragment must be unique among all the lists for fragments of the same table or index.
- Interval-based fragmentation
- This type of fragmentation partitions data into fragments that
are based on quantified values within a specific interval within the
range of fragment key of a single numeric, DATE, or DATETIME column
in the same fragment. You specify at least one range expression as
the fragment expression that defines the upper limit of fragment
key values for each fragment, and an interval expression that
specifies the size of the range of system-defined fragments that the
database server creates automatically.
You can optionally define a NULL fragment to store rows with missing data in the fragment key column, but no remainder fragment is supported or needed. The database server automatically creates a new fragment to store rows with non-NULL fragment key values outside the range of any existing fragment. The fragments that you define with range expressions are called range fragments, and the system-defined fragments that the database server creates at runtime are called interval fragments. This type of distribution scheme is sometimes called a range interval distribution strategy.