Policies for Partitioning Tables

Partition by Range

PARTITION BY RANGE specifies that each row is mapped to a partition based on the first VALUES LESS THAN subclause that evaluates to true, where VALUES LESS THAN clauses are sorted from lowest to highest.

PARTITION BY RANGE (column_name) [ STORE IN storage_group ]
( [ PARTITION partition_name VALUES LESS THAN (literal | MAXVALUE) [ STORE IN storage_group ]... )

The first, optional, STORE IN storage_group clause designates a storage group for the table, if no storage group is specified after the VALUES LESS THAN clause for the partition. Subsequent, optional, STORE IN storage_group clauses, specified per VALUES LESS THAN clause, may specify a storage group to use for the given partition_name, if the MAP PARTITION command was not already used to associate the specific partition name with a storage group. The MAXVALUE keyword specifies a partition for values not evaluating to true for any of the VALUES LESS THAN clauses. If MAXVALUE is not specified, then values not evaluating to true for any of the VALUES LESS THAN clauses would cause an error. A partition_name may not be used in more than one VALUES LESS THAN clause. A literal may not be used in more than one VALUES LESS THAN clause.

Determining the storage group associated with the partitioning criterion for an insertion into a PARTITION BY RANGE table is done in the following order:

  1. If an optional storage group is specified after the VALUES LESS THAN clause, then that storage group is used.
  2. If a storage group was specified in the optional STORE IN clause after the PARTITION BY RANGE clause, use that storage group.
  3. If the partition name specified by PARTITION partition_name was previously associated with a storage group using MAP PARTITION, then use that storage group.
  4. No storage group is found and an exception is thrown.

Only one column can be used for partitioning. The type of the partitioning column can be any supported SQL type except BLOB or CLOB. If the table has a PRIMARY KEY and/or UNIQUE index, the partitioning column must be added as a column for the index to ensure uniqueness across multiple partitions. The exception to this is a partitioning column that is defined as GENERATED ALWAYS AS IDENTITY. This column would not be required to be added to the PRIMARY KEY and/or UNIQUE index.

Partition by List

PARTITION BY LIST specifies that each row is mapped to a partition based on the first VALUES IN clause that contains the value stored in the partitioning column specified by column_name.

PARTITION BY LIST (column_name) [STORE IN storage_group ]
( [ PARTITION partition_name VALUES IN ( { literal [,literal]... } | DEFAULT ) [ STORE IN storage_group ] ]... )

Like PARTITION BY RANGE, The first, optional, STORE IN storage_group clause designates a storage group for the table, if no storage group is specified after the VALUES IN clause for the partition. Subsequent, optional, STORE IN storage_group clauses, specified per VALUES IN clause, may specify a storage group to use for the given partition_name, if the MAP PARTITION command was not already used to associate the specific partition name with a storage group name. If a value in the partitioning column specified by column_name does not match any of the VALUES IN lists, then an exception is thrown. The DEFAULT keyword specifies a partition for values not evaluating to true for any of the previous VALUES IN clauses. If DEFAULT is not specified, then values not evaluating to true for any of the VALUES IN clauses would cause an error. A partition_name may not be used in more than one VALUES IN clause. A literal may not be used in more than one VALUES IN clause.

Determining the storage group associated with the partitioning criterion for an insertion into a PARTITION BY LIST table is done in the following order:

  1. If an optional storage group is specified after the VALUES IN clause, then that storage group is used.
  2. If a storage group was specified in the optional STORE IN clause after the PARTITION BY LIST clause, use that storage group.
  3. If the partition specified by PARTITION partition_name was previously mapped to a storage group using MAP PARTITION, then use that storage group.
  4. No storage group is found and an exception is thrown.

Only one column can be used for partitioning. The type of the partitioning column can be any supported SQL type except BLOB or CLOB.

For more information, see Table Partitioning on the CREATE TABLE page.