Updated on 2024-09-06 GMT+08:00

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
Table 1 Parameters

Parameter

Description

table_name

The name of the table to be created.

expr

The expression of the partition. Currently, only the INT type is 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.

Example

Create a HASH-HASH partitioned table:
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;