HASH - PolarDB - Alibaba Cloud Documentation Center

This topic describes how to create a hash partitioned table.

Syntax

The following statement is used to create one or more hash partitioned tables. Data is partitioned based on hash rules. Expressions can be used to process partition key column values.

CREATE TABLE ... PARTITION BY [LINEAR] HASH(expr) [PARTITIONS number]
( PARTITION partition_name1,
  PARTITION partition_name2,...);

Parameters

Parameter	Description
expr	The expression of the partition. It must be of the INT type. The string type is not supported.
number	The number of hash partitions.
partition_name	The name of the partition. The name must be unique within the table.

Description

The hash algorithm performs a modulo operation on the number of partitions.

Hash partitions support the extended data type of LINEAR HASH. LINEAR HASH differs from HASH in that LINEAR HASH uses a linear, quadratic, or exponential algorithm, while HASH uses a modulus of the value of the hash function. Syntactically, the only difference between LINEAR HASH and HASH is that keywords are added to the PARTITION BY clause for LINEAR HASH.

Examples

Create a hash partitioned table:

CREATE TABLE sales_hash
(
  s_id        INT,
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT,
  PRIMARY KEY(s_id)
)PARTITION by HASH (s_id)
PARTITIONS 7;

Create a linear hash partitioned table:

CREATE TABLE sales_linear_hash
(
  s_id        INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT,
  PRIMARY KEY(s_id)
)PARTITION by LINEAR HASH (s_id)
PARTITIONS 7;