List fragment clause

Use the List Fragment clause to specify a list of fragment key values to store in the same fragment. The rows assigned to each fragment must match the fragment key value (or one of a comma-separated list of fragment key values) that defines the fragment.

This syntax fragment is part of the FRAGMENT BY clause.

Read syntax diagramSkip visual syntax diagram
List Fragment Clause

   V                                                               |   
|----PARTITION--partition--| List Expression Clause |--IN--dbspace-+-->

   |  (1)                                                  |   

List Expression Clause

                V            |        
           |  (1)                 |   

  1. Use this path no more than once
Element Description Restrictions Syntax
const_expr Constant expression that defines the list of fragment key values for the fragment to store 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. Constant Expressions
dbspace dbspace to store the fragment You can specify no more than 2,048 dbspaces. All of these dbspaces must have the same page size. Identifier
partition Name that you declare here for a fragment Must be unique among the names of fragments of the same object. If a table and its index use the same list fragmentation strategy, each index fragment must have the same name as the corresponding table fragment. Identifier

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.

REMAINDER and NULL fragments in list-based storage distribution

You can optionally define a REMAINDER fragment to store rows that do not match the list of fragment key values for any fragment.

You can optionally define a NULL fragment to store rows with missing fragment key values by specifying only IS NULL or NULL after the VALUES keyword in the List Expression clause for the fragment. You cannot include NULL or IS NULL in an expression list that also includes any other expression. (In this context, NULL and IS NULL are keyword synonyms.)

If no NULL fragment is defined, and an operation attempts to insert a row that is missing data for the fragment key, the result depends on whether a REMAINDER fragment exists:
  • If a REMAINDER fragment is defined, the row is stored in the REMAINDER fragment.
  • If no REMAINDER fragment is defined, the database server issues an exception.

If no REMAINDER fragment is defined, and an INSERT, UPDATE, MERGE, or other DML operation attempts to store a row whose fragment key does not match a list value for any fragment, the database server issues an exception.

When you define a list-based partitioning scheme for a table or index, the fragment list can include no more than one NULL fragment, and no more than one REMAINDER fragment.

If a table that is partitioned BY LIST has no NULL or REMAINDER fragment, but you subsequently determine that either or both of these fragments are needed, you can add a NULL fragment or a REMAINDER fragment, or both, to the fragment list by using the ADD option to the ALTER FRAGMENT statement. For more information, see ADD Clause.

Copyright© 2019 HCL Technologies Limited