Fragmentation by LIST

A list fragmentation strategy partitions data into a set of fragments that are each defined by a list of discrete values of the fragment key. Every expression must be a quoted string or a literal value. Each value in the list must be unique among the lists for fragments of the same object.

Fragmenting by list resembles fragmentation by expression (where the fragment expressions include the IN operator or the logical OR operator) in these respects:

Every non-REMAINDER fragment stores rows for which the fragment key values matches the fragment expression.
You can optionally define a REMAINDER fragment.
You can optionally define a NULL fragment.

As the name implies, however, fragmentation by list defines each fragment by a list of fragment expressions, rather than restricting each fragment to a single expression.

The syntax for defining a list fragmentation strategy requires one or more list fragments of the following form.

   FRAGMENT BY LIST  
      PARTITION partition VALUES (expression_list) IN dbspace,
      . . . 
      PARTITION partition VALUES (expression_list) IN dbspace,
      PARTITION partition VALUES (NULL) IN dbspace,
      PARTITION partition REMAINDER IN dbspace

Here the last two partitions (whose expressions define a NULL fragment and a REMAINDER fragment) are not required.

As with other fragmentation schemes, each PARTITION partition specification declares the unique name of a fragment. The (expression_list) specification is the comma-separated list of one or more constant expressions that defines each list fragment, and the IN dbspace specification identifies the storage location of the fragment.

You can optionally define a NULL fragment by specifying NULL as the only expression in the expression_list. You cannot include NULL in an expression list with other values that define the same fragment.

An alternative syntax notation for defining the NULL fragment is VALUES IS NULL (with no delimiting parentheses) as the only expression for a fragment. The digit 0 is not equivalent to the NULL or IS NULL keywords.

Just as in expression-based fragmentation, you can optionally define a REMAINDER fragment for rows that match none of the specified fragment expressions. If you define a REMAINDER fragment but no NULL fragment, rows with the fragment key value missing are stored in the REMAINDER fragment. The database server issues an exception for INSERT operations if the fragment key value for an inserted row matches no fragment expression, and no REMAINDER fragment is defined. An exception is similarly issued if data us missing from the fragment key column, but the fragment list includes no NULL fragment and no REMAINDER fragment.

When you use the CREATE INDEX statement to define an index on a table that is fragmented by list, it is not necessary to include the FRAGMENT BY or PARTITION BY clause to create indexes that use the same list fragmentation strategy as their table. By default, the database server partitions the index by the same list fragmentation strategy as its table, and declares for each index fragment the same name that you specified after the PARTITION keyword for the corresponding table fragment.

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 expression lists that define fragments of the same table or index. The database server issues an error if the lists of expressions for two list fragments include the same fragment key value. This uniqueness requirement for fragment expressions simplifies fragment elimination in queries, if the fragment expressions correspond to query predicates and filters that support fragment elimination.

A list fragmentation strategy is most effective when the fragment key for a table has finite set of values, and queries on the table specify equality predicates on the fragment key. For a table whose fragment key is a numeric or time data type with a range of possible values that resembles a continuum, an interval fragmentation scheme is recommended, rather than list fragmentation.