Fragmentation by EXPRESSION

For a table that uses an expression-based distribution scheme, the rows that the database server stores in an insert or load operation are distributed among a user-defined number of fragments, in which each fragment is defined by a Boolean expression for the fragment key.

The fragment expression must be a column expression. This can be the same column (or the same set of columns) for all of the fragments, or different fragments can be defined with different keys. The expression can only reference columns in the table that is being fragmented. Subqueries or calls to user-defined routines are not valid.

The syntax for defining an expression fragmentation strategy defines one or more expression fragments of this form:

   FRAGMENT BY EXPRESSION  
      PARTITION partition expression IN dbspace,
      . . . 
      PARTITION partition expression IN dbspace,
      PARTITION partition VALUES (NULL) IN dbspace,
      PARTITION partition REMAINDER IN dbspace

As in other fragmentation schemes, each PARTITION partition specification declares the unique name of a fragment. The expression specification defines the fragment expression, and the IN dbspace specification defines the storage location for the fragment. You can optionally define a NULL fragment by specifying NULL as the expression.

You also can optionally define a REMAINDER fragment for rows that match none of the specified fragment expressions. For some queries, the REMAINDER fragment might be difficult to eliminate, and for some tables, the REMAINDER fragment might become quite large, but the database server issues an exception if the fragment key value for an inserted row matches no fragment expression, and no REMAINDER fragment is defined.

You can optionally define a NULL fragment to stores rows in which the fragment key value is missing.

During an insert into a table that is fragmented by expression, the database server takes these actions:

1. The fragment key value for the row is evaluated.
2. The fragment expression for each fragment is evaluated and compared to the fragment key value for the row, beginning with the fragment whose sysfragments.evalpos value in the system catalog is lowest.
3. If there is no match, the previous step is repeated for the fragment with next highest sysfragments.evalpos value.
4. This continues until the first match is found between the fragment key value and a fragment expression, after which the row is stored in the matching fragment.
5. If no match is found in the entire list of fragments, the row is stored in the REMAINDER fragment. (In this case of a row with an unmatched fragment key, if no REMAINDER is defined, an exception is issued.)

For expression-based fragmentation schemes that define overlapping fragment expressions, the storage location of rows that match the fragment expression of more than one fragment is dependent on the evalpos value for that fragment. You can avoid this dependency by only defining non-overlapping fragment expressions.

The evalpos value of a fragment is determined by its position in the initial fragment list within the FRAGMENT BY EXPRESSION or PARTITION BY EXPRESSION clause that defined the storage distribution of the table. Any new fragments added by ALTER FRAGMENT operations are assigned, by default, the next higher evalpos value (and will therefore be evaluated last during INSERT operations) unless you explicitly specify a position with the BEFORE or AFTER keyword. In this case, the evalpos value for the new fragment will be the ordinal position where was inserted into the fragment list. For tables that are fragmented by expression into a large number of fragments, you can achieve greater efficiency in INSERT an LOAD operations when fragments that are more likely to match fragment key values have relatively low evalpos values within the fragment list.

Fragmentation by expressions that creates nonoverlapping fragments on a single column can be an effective strategy for supporting fragment elimination in queries. The database server can eliminate fragments, for example, for queries with range expressions as well as queries with equality expressions if the query predicates correspond to fragment expressions. Expressions with relational operators and logical operators (or with both) can similarly be used for fragment expressions that match query filters.