This topic describes how to create a hash-hash partitioned table.

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

Parameters

Parameter Description
table_name The name of the table.
expr The expression of the partition. It must be of the INT type. The string type is not supported.
num The number of partitions. It is only valid for hash or key partitions.
sub_num The number of subpartitions. It is only valid for hash or key subpartitions.
partition_name The name of the partition. The name must be unique within the table.
subpartition_name The name of the subpartition. The name must be unique within the table.

Examples

Create a hash-hash partitioned table:
CREATE TABLE sales_hash_hash
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
   PARTITION BY HASH(month(date))
   SUBPARTITION BY HASH(TO_DAYS(date)) (
    PARTITION m0 (
           SUBPARTITION d0,
           SUBPARTITION d1
       ),
    PARTITION m1 (
           SUBPARTITION d2,
           SUBPARTITION d3
       ),
    PARTITION m2 (
           SUBPARTITION d4,
           SUBPARTITION d5
           )
       );