HASH-KEY

Constraints

• The definitions of a KEY partitioned table can be omitted. If PARTITIONS num is specified, that exact number of partition definitions are created. Otherwise, one partition definition is created by default.
• If you want to omit definitions of subpartitions, ensure that no definition is provided for any of the subpartitions. Otherwise, you need to specify the partition definition for each subpartition.

Syntax

The following statement is used to create one or more HASH-KEY partitioned tables where each partition may contain one or more subpartitions:

CREATE TABLE [ schema. ]table_name
 table_definition
   PARTITION BY [LINEAR] HASH(expr) [PARTITIONS num]
   SUBPARTITION BY [LINEAR] KEY(expr) [SUBPARTITIONS sub_num]
   (partition_definition [, partition_definition] ...);

partition_definition is:

PARTITION partition_name
    (subpartition_definition [, subpartition_definition] ...)

subpartition_definition is:

SUBPARTITION subpartition_name

Example

CREATE TABLE tbl_hash_key
(
    col1 INT,
    col2 INT,
    col3 varchar(20),
    col4 DATE
)
PARTITION BY HASH(col1) PARTITIONS 3
   SUBPARTITION BY KEY(col3) SUBPARTITIONS 2;