Fragmenting by LIST

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.

Fragmenting by list supports these 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 a table by list (or fragmenting an index, in the CREATE INDEX statement) must satisfy these requirements:

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 constant expression can be based on a single column or on multiple columns.
Lists cannot include duplicate constant expression values. Each value must be unique within the FRAGMENT BY LIST clause.

Load, INSERT, MERGE, or UPDATE operations on tables fragmented BY LIST can fail at runtime under these circumstances:

The fragment key for a row evaluates to NULL, but the FRAGMENT BY LIST clause defined no NULL fragment.
The fragment key for a row matches no constant expression value for any fragment, but no remainder fragment is defined.

The following is an example of a table fragmented by list:

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 example above, the table is fragmented on column state, which is called the fragment key or partitioning key. The fragment key can be a column expression:

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 lists of 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", "OR", "NM") 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. Unlike FRAGMENT BY EXPRESSION definitions, you cannot mix NULL and any other list value in the same LIST fragment definition. For example, the following VALUES list is not valid:

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.

LIST fragmentation in NLSCASE INSENSITIVE databases

In databases with the NLSCASE INSENSITIVE property, operations on NCHAR and NVARCHAR data ignore lettercase, so that the database server treats case variants among strings composed of same sequence letters as duplicates. If the fragment key is a NCHAR or NVARCHAR column, the list of character expressions that define a fragment also match any column values that are lettercase variants of those expressions in the fragmented table.

In following examples, ad_state column values with 'A' and 'a' values are stored in the part0 fragment/partition.

CREATE TABLE addr
   (
        ad_id NCHAR(100),
        ad_street NVARCHAR(255),
        ad_apt INT,
        ad_state NCHAR(2),
        ad_zip1 INT,
        ad_zip2 INT,
        checksum CHAR(48),
        PRIMARY KEY(ad_id)
   )
   FRAGMENT BY LIST(ad_state)
        PARTITION part0 VALUES ('A', 'B', 'C', 'D') IN dbs1,
        PARTITION part1 VALUES ('E', 'F', 'G', 'H') IN dbs2,
        PARTITION part2 VALUES ('I', 'J', 'K', 'L') IN dbs3,
        PARTITION part3 VALUES ('M', 'N', 'O', 'P') IN dbs4,
        PARTITION part4 VALUES ('Q', 'R', 'S', 'T') IN dbs5,
        PARTITION part5 REMAINDER IN dbs6 LOCK MODE ROW;

A query designed to return only rows with the values 'A' or 'a' could apply a filter on the ad_state column so that only the first fragment is scanned in the query execution plan:

SELECT * FROM addr WHERE ad_state = 'A';

The above case-insensitive query eliminates all of the fragments except part0 by scanning only that fragment, where any rows containing 'A' or 'a' are stored.

For more information on databases with the NLSCASE INSENSITIVE property, see CREATE DATABASE statement, Duplicate rows in NLSCASE INSENSITIVE databases, and NCHAR and NVARCHAR expressions in case-insensitive databases.