一. 功能描述
二级分区功能，即在原有的range分区、list分区、hash分区、interval分区的基础上再次进行分区。
二级分区可以对表中的每个分区再次进行分区。分区类型有range、list、hash三种，一级与二级分区的分区类型可以任意组合。二级分区支持使用CREATE/ALTER/SELECT语法，用于二级分区的创建与增删改查。二级分区的相关信息可在系统表PG_PARTITION中获取。
PG_PARTITION系统表存储数据库内所有分区表（partitioned table）、分区（table partition）、分区上toast表和分区索引（index partition）四类对象的信息。分区表索引（partitioned index）的信息不在PG_PARTITION系统表中保存。
表 1 PG_PARTITION字段
名称 | 类型 | 描述 |
oid | oid | 行标识符（隐含属性，必须明确选择）。 |
relname | name | 分区表、分区、分区上toast表和分区索引的名称。 |
parttype | "char" | 对象类型： |
'r'：partitioned table。 | ||
'p'：table partition。 | ||
's'：table subpartition。 | ||
'x'：index partition。 | ||
't'：toast table。 | ||
parentid | oid | 当对象为分区表或分区时，此字段表示分区表在PG_CLASS中的OID。 |
当对象为index partition时，此字段表示所属分区表索引（partitioned index）的OID。 | ||
partitionid | oid | 分区表的分区号。 |
rangenum | integer | 保留字段。 |
intervalnum | integer | 保留字段。 |
partstrategy | "char" | 分区表分区策略，现在仅支持： |
'r'：范围分区。 | ||
'v'：数值分区。 | ||
'i'：间隔分区。 | ||
'l'：list分区。 | ||
'h'：hash分区。 | ||
'n'：无效分区。 | ||
subpartstrategy | "char" | 子分区策略。 |
relfilenode | oid | table partition、index partition、分区上toast表的物理存储位置。 |
reltablespace | oid | table partition、index partition、分区上toast表所属表空间的OID。 |
relpages | double precision | 统计信息：table partition、index partition的数据页数量。 |
reltuples | double precision | 统计信息：table partition、index partition的元组数。 |
relallvisible | integer | 统计信息：table partition、index partition的可见数据页数。 |
reltoastrelid | oid | table partition所对应toast表的OID。 |
reltoastidxid | oid | table partition所对应toast表的索引的OID。 |
indextblid | oid | index partition对应table partition的OID。 |
indisusable | boolean | 分区索引是否可用。 |
reldeltarelid | oid | Delta表的OID。 |
reldeltaidx | oid | Delta表的索引表的OID。 |
relcudescrelid | oid | CU描述表的OID。 |
relcudescidx | oid | CU描述表的索引表的OID。 |
relfrozenxid | xid32 | 冻结事务ID号。 |
为保持前向兼容，保留此字段，新增relfrozenxid64用于记录此信息。 | ||
intspnum | integer | 间隔分区所属表空间的个数。 |
partkey | int2vector | 分区键的列号。 |
intervaltablespace | oidvector | 间隔分区所属的表空间，间隔分区以round-robin方式落在这些表空间内。 |
interval | text[] | 间隔分区的间隔值。 |
boundaries | text[] | 范围分区和间隔分区的上边界。 |
transit | text[] | 间隔分区的跳转点。 |
reloptions | text[] | 设置partition的存储属性，与pg_class.reloptions的形态一样，用“keyword=value”格式的字符串来表示，目前用于在线扩容的信息搜集。 |
relfrozenxid64 | xid | 冻结事务ID号。 |
relminmxid | xid | 冻结多事务ID号。 |
subpartkey | int2vector | 子分区键的列号。 |
subparttemplate | pg_node_tree | 临时子分区，可以避免增加分区时建在默认表空间。 |
relfrozenxid64 | xid | （”frozen”）事务ID替换。该字段用于跟踪此表是否需要为了防止事务ID重叠（或者允许收缩pg_clog）而进行清理。如果该关系不是表则为零（InvalidTransactionId）。 |
relminmxid | xid | 该表中所有在这个之前的多事务ID已经被一个事务ID替换。这用于跟踪该表是否需要为了防止多事务ID重叠或者允许收缩pg_clog而进行清理。如果该关系不是表则为零(InvalidTransactionId)。 |
二. 注意事项
interval分区不能作为二级分区。
三. 语法格式
创建、修改和删除二级分区的语法格式请参考：CREATE TABLE SUBPARTITION、ALTER TABLE SUBPARTITION。用户也可以使用如下命令在gsql客户端中查询相关SQL语法的使用帮助信息。
\h create table subpartition
\h alter table subpartition
说明：二级分区的增删改查：对二级分区表插入数据的语法与普通表的语法没有任何差别。当向二级分区表插入数据时，会根据一级分区和二级分区规则，把数据分布到匹配的二级分区中进行存储。
以下列举了部分与二级分区相关的常用SQL句式。
- 新增一级与二级分区： ALTER TABLE table_name ADD partition_desc [ ( subpartition_desc [, ... ] ) ]
- 删除指定一级分区包括属于它的所有二级分区： ALTER TABLE table_name DROP PARTITION { partition_name | { FOR （ value [ , … ] ） }
- 为指定一级分区新增二级分区： ALTER TABLE table_name MODIFY PARTITION partition_name ADD subpartition_desc
- 删除指定二级分区： ALTER TABLE table_name DROP SUBPARTITION { subpartition_name | { FOR （ value [ , … ] ） }
- 删除数据： DELETE FROM table_name [ PARTITION { ( partition_name ) | { FOR ( value [ , ... ] ) } } ] [ WHERE ...]
- DELETE FROM table_name [ SUBPARTITION { ( partition_name ) | { FOR ( value [ , ... ] ) } } ] [ WHERE ...]
- 更新数据： UPDATE table_name [ PARTITION { ( partition_name ) | { FOR ( value [ , ... ] ) } } ] SET [ WHERE ...]
- UPDATE table_name [ SUBPARTITION { ( partition_name ) | { FOR ( value [ , ... ] ) } } ] SET [ WHERE ...]
- 在指定二级分区查找数据： SELECT ... FROM table_name SUBPARTITION (subpartition_name)
- 单独查询某个分区或子分区数据： SELECT ... FROM table_name PARTITION { ( partition_name ) | FOR ( value [ , ... ] ) }
- SELECT ... FROM table_name SUBPARTITION { ( subpartition_name ) | {FOR ( value [, … ] ) }
- 当查询二级分区表的数据时（不能指定分区或子分区），如果查询条件中包含有分区键的条件，则在生成执行计划时会根据分区键条件过滤掉不符合条件的分区或子分区： EXPLAIN SELECT ... FROM table_name WHERE ...
四. 参数说明
- table_name
表名。
- partition_name
分区表的名称。
- subpartition_name
二级分区表的名称。
- SUBPARTITION BY [RANGE | LIST | HASH ]
根据column_name指定的字段进行二级分区，分区类型可以是RANGE、LIST、 HASH其一。
- SUBPARTITION TEMPLATE ( subpartition_desc [, … ])
常规子分区模板定义语法，适用于Range/List/Hash子分区，当分区定义中没有指定子分区的定义时会根据子分区模板自动生成二级分区。
若没有提供子分区模板，同时partition_desc也不指定subpartition_desc，则将创建一个默认子分区。
- hash_subpartition_by_quantity
Hash分区持有的子分区模板定义语法（也可用与子分区定义语法），指定创建N个Hash子分区，同时可选指定Hash子分区的tablespace名列表。
五. 示例
示例1
二级分区的创建，修改和删除。
1、创建一个分区表并插入数据。
create table t_part_list_range
( id number not null,
partition_key int,
subpartition_key int,
col2 varchar2(10)
)
partition by list(partition_key)
subpartition by range(subpartition_key)
(
partition t_partition_01 values (100)
(subpartition sub_1_1 values less than (10),
subpartition sub_1_2 values less than (20)
),
partition t_partition_02 values (200)
(subpartition sub_2_1 values less than (10),
subpartition sub_2_2 values less than (20)
)
);
insert into t_part_list_range values(1,100,5,'sub_1_1');
insert into t_part_list_range values(2,100,15,'sub_1_2');
insert into t_part_list_range values(3,200,5,'sub_2_1');
insert into t_part_list_range values(4,200,15,'sub_2_2');
insert into t_part_list_range values(5,200,16,'sub_2_2');
select * from t_part_list_range subpartition for (100,5);
2、新增一级与二级分区。
alter table t_part_list_range add partition t_partition_03 values (300)
( subpartition sub_3_1 values less than (10),
subpartition sub_3_2 values less than (20)
);
3、删除指定一级分区包括属于它的所有二级分区。
alter table t_part_list_range drop partition t_partition_02;
4、为指定一级分区新增二级分区。
alter table t_part_list_range modify partition t_partition_01 add subpartition sub_1_3 values less than (30);
5、删除指定二级分区。
alter table t_part_list_range drop subpartition sub_1_3;
示例2
一级分区为interval分区，二级分区为list分区，分区键类型为字符类型；创建并查看分区结果。
1、创建测试表，包含一级分区和二级分区。
CREATE TABLE t_subpartition_interval_list(
partition_key date,
subpartition_key varchar(20),
test varchar(20)
)partition by range(partition_key)
interval('12 month')
subpartition by list(subpartition_key)
(partition partition_p1 VALUES LESS THAN ('2019-01-01')
(subpartition sub_1_1 values('test1'),
subpartition sub_1_2 values('test2'),
subpartition sub_1_3 values (default)
),
partition partition_p2 VALUES LESS THAN ('2021-01-01')
(subpartition sub_2_1 values('test1'),
subpartition sub_2_2 values('test2'),
subpartition sub_2_3 values (default)
)
);
2、插入测试数据。
INSERT INTO t_subpartition_interval_list VALUES('2018-09-02','test','test1');
INSERT INTO t_subpartition_interval_list VALUES('2020-12-12','test2','test2');
INSERT INTO t_subpartition_interval_list VALUES('2022-02-02','interval','test3');
3、查询已有分区对应数据。
select * from t_subpartition_interval_list subpartition(sub_1_3);
select * from t_subpartition_interval_list subpartition(sub_2_2);
返回结果为：
partition_key | subpartition_key | test
---------------------+------------------+-------
2018-09-02 00:00:00 | test | test1
(1 row)
partition_key | subpartition_key | test
---------------------+------------------+-------
2020-12-12 00:00:00 | test2 | test2
(1 row)
4、查询表新增分区名。
select relname,parttype,partstrategy,boundaries
from pg_partition
where parentid = (select oid from pg_class where relname = 't_subpartition_interval_list')
union all
select relname,parttype,partstrategy,boundaries from pg_partition
where parentid in (
select oid from pg_partition
where parentid = (
select oid from pg_class where relname ='t_subpartition_interval_list'
)
order by relname)
order by relname;
返回结果为：
relname | parttype | partstrategy | boundaries
------------------------------+----------+--------------+-------------------------
partition_p1 | p | r | {2019-01-01}
partition_p2 | p | r | {2021-01-01}
sub_1_1 | s | l | {test1}
sub_1_2 | s | l | {test2}
sub_1_3 | s | l |
sub_2_1 | s | l | {test1}
sub_2_2 | s | l | {test2}
sub_2_3 | s | l |
sys_p1 | p | i | {"2023-01-01 00:00:00"}
sys_p1_subpartdefault1 | s | l |
t_subpartition_interval_list | r | i |
(11 rows)
5、查询新增分区对应数据。
select * from t_subpartition_interval_list subpartition(sys_p1_subpartdefault1);
返回结果为：
partition_key | subpartition_key | test
---------------------+------------------+-------
2022-02-02 00:00:00 | interval | test3
(1 row)
