partitioning-clause

PARTITION BY RANGE
Specifies that ranges of column values are used to determine the target data partition when inserting a row into the table. The number of partitions must not exceed 256. A table cannot be partitioned if it contains an identity column.
column-name
Specifies a column in the partitioning key. The partitioning key is used to determine into which partition in the table a row will be placed. Each column-name must be an unqualified name that identifies a column of the table. The same column must not be identified more than once.

Floating point, LOB, DataLink, and ROWID columns cannot be used in a partitioning key.

NULLS LAST
Indicates that null values compare high.
NULLS FIRST
Indicates that null values compare low.
PARTITION partition-name
Names the partition. A partition-name must not identify a data partition that was previously specified in the CREATE TABLE statement.

If the clause is not specified, a unique partition name is generated by the database manager.

boundary-spec
Specifies the boundaries of a range partition. The boundaries must be specified in ascending sequence. The ranges must not overlap.
starting-clause
Specifies the low end of the range for a data partition. The number of specified starting values must be the same as the number of columns in the partitioning key.
STARTING FROM
Introduces the starting-clause.
constant
Specifies a constant that must conform to the rules of a constant for the data type of the corresponding column of the partition key. If the corresponding column of the partition key is a distinct type, the constant must conform to the rules of the source type of the distinct type. The value must not be in the range of any other boundary-spec for the table.
MINVALUE
Specifies a value lower than the lowest possible value for the data type of the corresponding column of the partition key. If MINVALUE is specified, all subsequent values in the starting-clause must also be MINVALUE.
INCLUSIVE
Specifies that the specified range values are included in the data partition.
EXCLUSIVE
Specifies that the specified range values are excluded from the data partition. This specification is ignored when MINVALUE or MAXVALUE is specified.
ending-clause
Specifies the high end of the range for a data partition. The number of specified ending values must be the same as the number of columns in the data partitioning key.
ENDING AT
Introduces the ending-clause.
constant
Specifies a constant that must conform to the rules of a constant for the data type of the corresponding column of the partition key. If the corresponding column of the partition key is a distinct type, the constant must conform to the rules of the source type of the distinct type. The value must not be in the range of any other boundary-spec for the table.
MAXVALUE
Specifies a value higher than the highest possible value for the data type of the corresponding column of the partition key. If MAXVALUE is specified, all subsequent values in the ending-clause must also be MAXVALUE.
INCLUSIVE
Specifies that the specified range values are included in the data partition.
EXCLUSIVE
Specifies that the specified range values are excluded from the data partition. This specification is ignored when MINVALUE or MAXVALUE is specified.
EVERY integer-constant
Specifies that multiple data partitions will be added where integer-constant specifies the width of each data partition range. If EVERY is specified, only a single SMALLINT, INTEGER, BIGINT, DECIMAL, NUMERIC, DATE, or TIMESTAMP column can be specified for the partition key.

The starting value of the first data partition is the specified STARTING value. The starting value of each subsequent partition is the starting value of the previous partition + integer-constant. If the starting-clause specified EXCLUSIVE, the starting value of every partition is EXCLUSIVE. Otherwise, the starting value of every partition is INCLUSIVE.

The ending value of every partition of the range is (start + integer-constant - 1). If the ending-clause specified EXCLUSIVE, the ending value of every partition is EXCLUSIVE. Otherwise, the ending value of every partition is INCLUSIVE.

The number of partitions added is determined by adding integer-constant repeatedly to the STARTING value until the ENDING value is reached. For example:

  CREATE TABLE FOO
    (A  INT)
    PARTITION BY RANGE(A)
      (STARTING(1) ENDING(10) EVERY(2))

is equivalent to the following CREATE TABLE statement:

  CREATE TABLE FOO
    (A  INT)
    (PARTITION BY RANGE(A)
      (STARTING(1) ENDING(2),
       STARTING(3) ENDING(4),
       STARTING(5) ENDING(6),
       STARTING(7) ENDING(8),
       STARTING(9) ENDING(10))

In the case of dates and timestamps, the EVERY value must be a labeled duration. For example:

  CREATE TABLE FOO
    (A  DATE)
    PARTITION BY RANGE(A)
      (STARTING('2001-01-01') ENDING('2010-01-01') EVERY(3 MONTHS))
PARTITION BY HASH
Specifies that the hash function is used to determine the target data partition when inserting a row into the table. A table cannot be partitioned if it contains an identity column.
(column-name,...)
Specifies the partitioning key. The partitioning key is used to determine into which partition in the table a row will be placed. Each column-name must be an unqualified name that identifies a column of the table. The same column must not be identified more than once.

The columns that make up the partitioning key must be a subset of the columns that make up any unique constraints over the table. Floating point, LOB, date, time, timestamp, DataLink, and ROWID columns cannot be used in a partitioning key.

INTO integer PARTITIONS
Specifies the number of partitions. The number of partitions must not exceed 256.