# 二级分区
二级分区（Subpartition，也叫组合分区）是基本数据分区类型的组合，将表通过一种分区策略进行分区，然后使用第二种分区策略将每个分区进一步细分为二级分区。给定分区的所有二级分区表示数据的逻辑子集。常见的二级分区组合如下所示：
- Range-Range
- Range-List
- Range-Hash
- List-Range
- List-List
- List-Hash
- Hash-Range
- Hash-List
- Hash-Hash
 
示例如下：
```
--Range-Range
gaussdb=# 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)
    )
);
gaussdb=# DROP TABLE t_range_range;
--Range-List
gaussdb=# 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)
    )
);
gaussdb=# DROP TABLE t_range_list;
--Range-Hash
gaussdb=# 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)
);
gaussdb=# DROP TABLE t_range_hash;
--List-Range
gaussdb=# 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)
    )
);
gaussdb=# DROP TABLE t_list_range;
--List-List
gaussdb=# 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)
    )
);
gaussdb=# DROP TABLE t_list_list;
--List-Hash
gaussdb=# 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)
);
gaussdb=# DROP TABLE t_list_hash;
--Hash-Range
gaussdb=# 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)
    )
);
gaussdb=# DROP TABLE t_hash_range;
--Hash-List
gaussdb=# 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)
    )
);
gaussdb=# DROP TABLE t_hash_list;
--Hash-Hash
gaussdb=# 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
);
gaussdb=# DROP TABLE t_hash_hash;
-- list-list
gaussdb=# 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'))
  )
);
gaussdb=# SELECT * FROM pg_get_tabledef('list_list');
gaussdb=# INSERT INTO list_list VALUES('201902', '1', '120', 1);
gaussdb=# INSERT INTO list_list VALUES('201902', '2', '240', 1);
gaussdb=# INSERT INTO list_list VALUES('201902', '1', '120', 1);
gaussdb=# INSERT INTO list_list VALUES('201903', '2', '240', 1);
gaussdb=# INSERT INTO list_list VALUES('201903', '1', '120', 1);
gaussdb=# INSERT INTO list_list VALUES('201903', '2', '240', 1);
gaussdb=# SELECT * FROM list_list ORDER BY month_code;
gaussdb=# DROP TABLE list_list;
-- hash-range
gaussdb=# 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')
  )
);
gaussdb=# INSERT INTO hash_range VALUES('201901', '1', '1', 1);
gaussdb=# INSERT INTO hash_range VALUES('201901', '2', '1', 1);
gaussdb=# INSERT INTO hash_range VALUES('201901', '1', '1', 1);
gaussdb=# INSERT INTO hash_range VALUES('201903', '2', '1', 1);
gaussdb=# INSERT INTO hash_range VALUES('201903', '1', '1', 1);
gaussdb=# INSERT INTO hash_range VALUES('201903', '2', '1', 1);
gaussdb=# SELECT * FROM hash_range;
gaussdb=# DROP TABLE hash_range;
```
![](https://support.huaweicloud.com/distributed-devg-v10-gaussdb/public_sys-resources/note_3.0-zh-cn.png)
Interval分区可以看成是范围分区的一种特殊形式。