PolarDB for PostgreSQL (Compatible with Oracle) supports a simplified syntax for creating hash-partitioned tables by specifying the number of partitions directly.
Hash partitioning distributes rows across partitions by computing a hash value for the partition key column and assigning each row to a partition based on that value. This produces approximately equal-sized partitions without requiring you to define explicit value ranges.
Create hash partitions
CREATE TABLE ... PARTITION BY HASH (part_params) PARTITIONS number;| Parameter | Description |
|---|---|
part_params | The partition key column. |
number | The number of hash partitions to create. |
Example: The following statement creates a table with four hash partitions on deptno.
CREATE TABLE part_hash (deptno NUMBER, deptname VARCHAR(32))
PARTITION BY HASH(deptno) PARTITIONS 4;Create hash subpartitions
Use hash subpartitioning to further divide each partition of a RANGE-, LIST-, or HASH-partitioned table.
CREATE TABLE ... PARTITION BY part_strategy (part_params)
SUBPARTITION BY HASH (part_params) SUBPARTITIONS number
(table_partitions_list);| Parameter | Description |
|---|---|
part_strategy | The partitioning type of the top-level partitions. Valid values: RANGE, LIST, HASH. |
part_params | The partition key column. |
number | The number of hash subpartitions per partition. |
table_partitions_list | The list of partition definitions. |
RANGE partitioning with HASH subpartitions
The following statement partitions a sales table by quarter (time_id) and creates four hash subpartitions per quarter based on cust_id. The first partition (sales_q1_2006) defines three named subpartitions; the remaining partitions use the default count of four.
CREATE TABLE part_test
(prod_id NUMBER(6),
cust_id NUMBER,
time_id DATE,
channel_id CHAR(1),
promo_id NUMBER(6),
quantity_sold NUMBER(3),
amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)
SUBPARTITIONS 4
(PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
(SUBPARTITION q1_europe,
SUBPARTITION q1_asia,
SUBPARTITION q1_americas
),
PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy')),
PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy')),
PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
);HASH partitioning with HASH subpartitions
The following statement creates a table with four hash partitions on prod_id, each subdivided into four hash subpartitions on cust_id.
CREATE TABLE part_test
(prod_id NUMBER(6),
cust_id NUMBER,
time_id DATE,
channel_id CHAR(1),
promo_id NUMBER(6),
quantity_sold NUMBER(3),
amount_sold NUMBER(10,2)
)
PARTITION BY HASH (prod_id) SUBPARTITION BY HASH (cust_id)
PARTITIONS 4 SUBPARTITIONS 4;