HASH-HASH

Constraints

• The definitions of a HASH 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-HASH 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] HASH(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_hash
(
    col1 INT,
    col2 INT,
    col3 varchar(20),
    col4 DATE
)
PARTITION BY HASH(col1) PARTITIONS 9
   SUBPARTITION BY HASH(col2) SUBPARTITIONS 3;