Help Center/
GaussDB/
Feature Guide(Centralized_V2.0-8.x)/
Partitioned Table/
Introduction to Partitioned Tables/
Partitioning Policy/
Subpartitioning
Updated on 2025-05-29 GMT+08:00
Subpartitioning
Subpartitioning (also referred to as composite partitioning) is a combination of basic data partitioning types. A table is partitioned by one data distribution method and then each partition is further subdivided into new partitions using a second data distribution method. All new partitions of a given partition represent a logical subset of the data. Common types of composite partitioning are as follows:
- Range-Range
- Range-List
- Range-Hash
- List-Range
- List-List
- List-Hash
- Hash-Range
- Hash-List
- Hash-Hash
Example:
gaussdb=# --Range-Range CREATE TABLE t_range_range ( c1 INT, c2 INT, c3 INT ) PARTITION BY RANGE (c1) SUBPARTITION BY RANGE (c2) ( PARTITION p1 VALUES LESS THAN (10) ( SUBPARTITION p1sp1 VALUES LESS THAN (5), SUBPARTITION p1sp2 VALUES LESS THAN (10) ), PARTITION p2 VALUES LESS THAN (20) ( SUBPARTITION p2sp1 VALUES LESS THAN (15), SUBPARTITION p2sp2 VALUES LESS THAN (20) ) ); DROP TABLE t_range_range; --Range-List CREATE TABLE t_range_list ( c1 INT, c2 INT, c3 INT ) PARTITION BY RANGE (c1) SUBPARTITION BY LIST (c2) ( PARTITION p1 VALUES LESS THAN (10) ( SUBPARTITION p1sp1 VALUES (1, 2), SUBPARTITION p1sp2 VALUES (3, 4) ), PARTITION p2 VALUES LESS THAN (20) ( SUBPARTITION p2sp1 VALUES (1, 2), SUBPARTITION p2sp2 VALUES (3, 4) ) ); DROP TABLE t_range_list; --Range-Hash CREATE TABLE t_range_hash ( c1 INT, c2 INT, c3 INT ) PARTITION BY RANGE (c1) SUBPARTITION BY HASH (c2) SUBPARTITIONS 2 ( PARTITION p1 VALUES LESS THAN (10), PARTITION p2 VALUES LESS THAN (20) ); DROP TABLE t_range_hash; --List-Range CREATE TABLE t_list_range ( c1 INT, c2 INT, c3 INT ) PARTITION BY LIST (c1) SUBPARTITION BY RANGE (c2) ( PARTITION p1 VALUES (1, 2) ( SUBPARTITION p1sp1 VALUES LESS THAN (5), SUBPARTITION p1sp2 VALUES LESS THAN (10) ), PARTITION p2 VALUES (3, 4) ( SUBPARTITION p2sp1 VALUES LESS THAN (5), SUBPARTITION p2sp2 VALUES LESS THAN (10) ) ); DROP TABLE t_list_range; --List-List CREATE TABLE t_list_list ( c1 INT, c2 INT, c3 INT ) PARTITION BY LIST (c1) SUBPARTITION BY LIST (c2) ( PARTITION p1 VALUES (1, 2) ( SUBPARTITION p1sp1 VALUES (1, 2), SUBPARTITION p1sp2 VALUES (3, 4) ), PARTITION p2 VALUES (3, 4) ( SUBPARTITION p2sp1 VALUES (1, 2), SUBPARTITION p2sp2 VALUES (3, 4) ) ); DROP TABLE t_list_list; --List-Hash CREATE TABLE t_list_hash ( c1 INT, c2 INT, c3 INT ) PARTITION BY LIST (c1) SUBPARTITION BY HASH (c2) SUBPARTITIONS 2 ( PARTITION p1 VALUES (1, 2), PARTITION p2 VALUES (3, 4) ); DROP TABLE t_list_hash; --Hash-Range CREATE TABLE t_hash_range ( c1 INT, c2 INT, c3 INT ) PARTITION BY HASH (c1) PARTITIONS 2 SUBPARTITION BY RANGE (c2) ( PARTITION p1 ( SUBPARTITION p1sp1 VALUES LESS THAN (5), SUBPARTITION p1sp2 VALUES LESS THAN (10) ), PARTITION p2 ( SUBPARTITION p2sp1 VALUES LESS THAN (5), SUBPARTITION p2sp2 VALUES LESS THAN (10) ) ); DROP TABLE t_hash_range; --Hash-List CREATE TABLE t_hash_list ( c1 INT, c2 INT, c3 INT ) PARTITION BY HASH (c1) PARTITIONS 2 SUBPARTITION BY LIST (c2) ( PARTITION p1 ( SUBPARTITION p1sp1 VALUES (1, 2), SUBPARTITION p1sp2 VALUES (3, 4) ), PARTITION p2 ( SUBPARTITION p2sp1 VALUES (1, 2), SUBPARTITION p2sp2 VALUES (3, 4) ) ); DROP TABLE t_hash_list; --Hash-Hash CREATE TABLE t_hash_hash ( c1 INT, c2 INT, c3 INT ) PARTITION BY HASH (c1) PARTITIONS 2 SUBPARTITION BY HASH (c2) SUBPARTITIONS 2 ( PARTITION p1, PARTITION p2 ); DROP TABLE t_hash_hash; -- list-list CREATE TABLE list_list ( month_code VARCHAR2 ( 30 ) NOT NULL , dept_code VARCHAR2 ( 30 ) NOT NULL , user_no VARCHAR2 ( 30 ) NOT NULL , sales_amt int ) PARTITION BY LIST (month_code) SUBPARTITION BY LIST (dept_code, user_no) ( PARTITION p_201901 VALUES ( '201902' ), PARTITION p_201902 VALUES ( '201903' ) ( SUBPARTITION p_201902_a VALUES (('1','120')), SUBPARTITION p_201902_b VALUES (('2','240')) ) ); SELECT * FROM pg_get_tabledef('list_list'); INSERT INTO list_list VALUES('201902', '1', '120', 1); INSERT INTO list_list VALUES('201902', '2', '240', 1); INSERT INTO list_list VALUES('201902', '1', '120', 1); INSERT INTO list_list VALUES('201903', '2', '240', 1); INSERT INTO list_list VALUES('201903', '1', '120', 1); INSERT INTO list_list VALUES('201903', '2', '240', 1); SELECT * FROM list_list ORDER BY month_code; DROP TABLE list_list; -- hash-range CREATE TABLE hash_range ( month_code VARCHAR2 ( 30 ) NOT NULL , dept_code VARCHAR2 ( 30 ) NOT NULL , user_no VARCHAR2 ( 30 ) NOT NULL , sales_amt int ) PARTITION BY hash (month_code) SUBPARTITION BY range (dept_code,user_no) ( PARTITION p_201901, PARTITION p_201902 ( SUBPARTITION p_201902_a VALUES LESS THAN ( '2' ,'2'), SUBPARTITION p_201902_b VALUES LESS THAN ( '3' ,'4') ) ); INSERT INTO hash_range VALUES('201901', '1', '1', 1); INSERT INTO hash_range VALUES('201901', '2', '1', 1); INSERT INTO hash_range VALUES('201901', '1', '1', 1); INSERT INTO hash_range VALUES('201903', '2', '1', 1); INSERT INTO hash_range VALUES('201903', '1', '1', 1); INSERT INTO hash_range VALUES('201903', '2', '1', 1); SELECT * FROM hash_range; DROP TABLE hash_range;

Interval partitioning is a special form of range partitioning. Currently, interval partitioning cannot be defined in subpartitioning.
Parent topic: Partitioning Policy
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
The system is busy. Please try again later.
For any further questions, feel free to contact us through the chatbot.
Chatbot