创建和管理分区表
背景信息
Vastbase数据库支持的分区表：Range分区表、List分区表和Hash分区表。
- Range分区表：范围分区根据您为每个分区建立的分区键值的范围将数据映射到分区。这种分区方式是最常见的，并且分区键值经常采用日期，例如将销售数据按照月份进行分区。
- List分区表：将数据中包含的键值分别存储在不同的分区中，依次将数据映射到每一个分区，分区中包含的键值由创建分区表时指定。
- Hash分区表：将数据根据内部哈希算法依次映射到每一个分区中，包含的分区个数由创建分区表时指定。
分区表和普通表相比具有以下优点：
改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索效率。
增强可用性：如果分区表的某个分区出现故障，表在其他分区的数据仍然可用。
方便维护：如果分区表的某个分区出现故障，需要修复数据，只修复该分区即可。
均衡I/O：可以把不同的分区映射到不同的磁盘以平衡I/O，改善整个系统性能。
普通表若要转成分区表，需要新建分区表，然后把普通表中的数据导入到新建的分区表中。因此在初始设计表时，请根据业务提前规划是否使用分区表。
操作步骤
示例一：按照以下方式对Range分区表进行操作。
1、创建分区表。
CREATE TABLE customer_address
(
ca_address_sk integer NOT NULL ,
ca_address_id character(16) NOT NULL ,
ca_street_number character(10) ,
ca_street_name character varying(60) ,
ca_street_type character(15) ,
ca_suite_number character(10) ,
ca_city character varying(60) ,
ca_county character varying(30) ,
ca_state character(2) ,
ca_zip character(10) ,
ca_country character varying(20) ,
ca_gmt_offset numeric(5,2) ,
ca_location_type character(20)
)
TABLESPACE example1
PARTITION BY RANGE (ca_address_sk)
(
PARTITION P1 VALUES LESS THAN(5000),
PARTITION P2 VALUES LESS THAN(10000),
PARTITION P3 VALUES LESS THAN(15000),
PARTITION P4 VALUES LESS THAN(20000),
PARTITION P5 VALUES LESS THAN(25000),
PARTITION P6 VALUES LESS THAN(30000),
PARTITION P7 VALUES LESS THAN(40000),
PARTITION P8 VALUES LESS THAN(MAXVALUE)
TABLESPACE example2
)
ENABLE ROW MOVEMENT;
当结果显示为如下信息，则表示创建成功:
CREATE TABLE
创建列存分区表的数量建议不超过1000个。
2、插入数据。
将表customer_address的数据插入到表web_returns_p2中。
例如在数据库中创建了一个表customer_address的备份表tpcds.web_returns_p2，现在需要将表tpcds.customer_address中的数据插入到表tpcds.web_returns_p2中，则可以执行如下命令。
创建备份表web_returns_p2并插入数据。
CREATE TABLE web_returns_p2
(
ca_address_sk integer NOT NULL ,
ca_address_id character(16) NOT NULL ,
ca_street_number character(10) ,
ca_street_name character varying(60) ,
ca_street_type character(15) ,
ca_suite_number character(10) ,
ca_city character varying(60) ,
ca_county character varying(30) ,
ca_state character(2) ,
ca_zip character(10) ,
ca_country character varying(20) ,
ca_gmt_offset numeric(5,2) ,
ca_location_type character(20)
)
TABLESPACE example1
PARTITION BY RANGE (ca_address_sk)
(
PARTITION P1 VALUES LESS THAN(5000),
PARTITION P2 VALUES LESS THAN(10000),
PARTITION P3 VALUES LESS THAN(15000),
PARTITION P4 VALUES LESS THAN(20000),
PARTITION P5 VALUES LESS THAN(25000),
PARTITION P6 VALUES LESS THAN(30000),
PARTITION P7 VALUES LESS THAN(40000),
PARTITION P8 VALUES LESS THAN(MAXVALUE) TABLESPACE example2
)
ENABLE ROW MOVEMENT;
INSERT INTO web_returns_p2 SELECT * FROM customer_address;
6、重命名分区。
ALTER TABLE web_returns_p2 RENAME PARTITION P8 TO P_9;
ALTER TABLE web_returns_p2 RENAME PARTITION FOR (40000) TO P8;
7、查询分区P6。
SELECT * FROM web_returns_p2 PARTITION (P6);
SELECT * FROM web_returns_p2 PARTITION FOR (35888);
8、删除分区表和表空间。
DROP TABLE web_returns_p2;
DROP TABLE customer_address;
示例二：按照以下方式对Hash分区表进行操作。
创建分区表（ Hash分区只支持1个分区键，分区键值能用表的普通字段来指定，分区范围的值只支持常量表达式、数值或字符串常量，不支持新增和删除分区。） 语法形式如下：
CREATE TABLE [IF NOT EXISTS] table_name
…
PARTITION BY HASH (columns_name)
hash_partition_desc
…
1、创建分区表。
CREATE TABLE t_hash_1
(c1 integer,
c2 date,
c3 text)
PARTITION BY HASH (c1)
(
PARTITION t_hash_p1,
PARTITION t_hash_p2
);
2、插入数据并查询结果。
insert into t_hash_1 values(1,'2020-07-29','a');
SELECT * FROM t_hash_1 PARTITION (t_hash_p1);
返回结果为：
c1 | c2 | c3
----+---------------------+----
1 | 2020-07-29 00:00:00 | a
(1 row)
3、更新数据并查询结果。
UPDATE t_hash_1 set c1=3 where c1=1;
SELECT * FROM t_hash_1 PARTITION (t_hash_p2);
返回结果为：
c1 | c2 | c3
----+---------------------+----
3 | 2020-07-29 00:00:00 | a
(1 row)
4、删除数据并查询结果。
DELETE t_hash_1 where c1=3;
SELECT * FROM t_hash_1;
返回结果为：
c1 | c2 | c3
----+----+----
(0 rows)
示例三：按照以下方式对List分区表进行操作。
1、创建分区表。
CREATE TABLE t_list
(c1 integer,
c2 date,
c3 text)
PARTITION BY LIST (c2)
(
PARTITION p1 VALUES ('2019-10-12'),
PARTITION p2 VALUES ('2019-10-13'),
PARTITION p3 VALUES ('2019-10-14')
);
2、修改分区表行迁移属性。
alter table t_list enable row movement;
3、新增分区。
ALTER TABLE t_list ADD PARTITION P4 VALUES ('2019-10-15');
4、删除分区。
ALTER TABLE t_list DROP PARTITION p4;
5、插入数据并查询结果。
insert into t_list values(1,'2019-10-13','test');
SELECT * FROM t_list PARTITION (p2);
返回结果为：
c1 | c2 | c3
----+---------------------+------
1 | 2019-10-13 00:00:00 | test
(1 row)
查看其它的分区：
SELECT * FROM t_list PARTITION (p1);
返回结果为：
c1 | c2 | c3
----+----+----
(0 rows)
6、更新数据并查询结果。
update t_list set c2='2019-10-12' where c1=1;
SELECT * FROM t_list PARTITION (p2);
返回结果为：
c1 | c2 | c3
----+----+----
(0 rows)
SELECT * FROM t_list PARTITION (p1);
返回结果为：
c1 | c2 | c3
----+---------------------+------
1 | 2019-10-12 00:00:00 | test
(1 row)
支持default分区
功能描述
针对Range、List和Hash类型的分区表，支持定义和使用default分区。
Range类型的分区表，可以通过指定分区范围值为MAXVALUE来包含所有情况的值。
Hash类型的分区表，由于Hash分区表的特性，必然可以包含所有情况的值。
语法格式
1）创建分区表时指定创建default分区，defaultf分区必须定义为最后一个分区，SQL语法如下：
CREATE TABLE [IF NOT EXISTS] table_name
...
(
{PARTITION partition_name VALUES ( value […])[TABLESPACE tablespace _name],}[…]
PARTITION partition_name VALUES (DEFAULT)[TABLESPACE tablespace_name]
)
...
2）新增default分区(已存在default分区的LIST分区不能新增分区)SQL语法如下：
ALTER TABLE table_name ADD PARTITION partition_name VALUES (DEFAULT)
[TABLESPACE tablespace_name];
3）删除default分区(不能只剩下一个default分区)SQL语法如下：
ALTER TABLE table_name DROP PARTITION partition_name;
示例
1、创建一个分区表。
create table t_partition_list4(col number,name varchar2(20))
partition by list(col)(
partition t_list_p1 values(1,3,5,7,9) ,
partition t_list_p2 values(2,4,6,8,10) );
2、添加一个默认分区并插入数据。
alter table t_partition_list4 add partition t_list_default values(default);
insert into t_partition_list4 values(1,'t_list_p1');
insert into t_partition_list4 values(2,'t_list_p2');
insert into t_partition_list4 values(31,'t_list_default');
3、查询分区表中的数据t_list_p1。
select * from t_partition_list4 partition(t_list_p1);
返回结果为：
col | name
-----+-----------
1 | t_list_p1
(1 row)
查询分区表中的数据t_list_p2。
select * from t_partition_list4 partition(t_list_p2);
返回结果为：
col | name
-----+-----------
2 | t_list_p2
(1 row)
t_list_default
select * from t_partition_list4 partition(t_list_default)
返回结果为：
col | name
-----+----------------
31 | t_list_default
(1 row)
支持两级分区
功能描述
二级分区功能，即可以在原有的range、list、hash分区的基础上再次进行分区。
二级分区可以对表中的每个分区再次进行分区。分区类型有range、list、hash三种，一级与二级分区的分区类型可以任意组合。二级分区可以支持CREATE/ALTER/SELECT语法，用于二级分区的创建与增删改查。二级分区的相关信息可在系统表pg_partition中获取。
语法格式
创建二级分区表语法中二级分区相关语法如下：
CREATE TABLE
...
PARTITION BY [ RANGE | LIST | HASH ] ( column_name [, ... ] )
SUBPARTITION BY [ RANGE | LIST | HASH ] ( column_name [, ... ] )
[{SUBPARTITION TEMPLATE ( subpartition_desc [, ... ] ) } | hash subpartitions_by_quantity ]
{ ( { partition_desc [ ( subpartition_desc [, ... ] ) | hash_subpartitions_by_quantity ] } [, ... ] ) | hash_partition_by_quantity }
[ { ENABLE | DISABLE } ROW MOVEMENT ]
...
where partition_desc or subpartition_desc can be:
{ { PARTITION partition_name | SUBPARTITION subpartition_name }
[ { { VALUES LESS THAN ( { value | MAXVALUE } [, ... ] ) }
| { START ( { value | MAXVALUE } [, ... ] ) END ( { value | MAXVALUE } [, ... ] ) }
| { START ( { value | MAXVALUE } [, ... ] ) }
| { END ( { value | MAXVALUE } [, ... ] ) }
| { VALUES ( value [, ... ] ) }
| { VALUES ( DEFAULT ) }
} ]
[TABLESOACE tablespace_name]
}
where hash_partition._by_quantity or hash_subpartitions_by_quantity can be:
{ PARTITIONS | SUBPARTITIONS } integer [ STORE IN ( tablespace_name [ , … ] ) ]
语法说明：
SUBPARTITION BY RANGE | LIST | HASH ：根据column_name指定的字段进行二级分区，分区类型可以是RANGE、LIST、 HASH其一。
SUBPARTITION TEMPLATE ( subpartition_desc [, … ])：常规子分区模板定义语法，适用于Range/List/Hash子分区，当分区定义中没有指定子分区的定义时会根据子分区模板自动生成二级分区。
hash_subpartition_by_quantity：Hash分区持有的子分区模板定义语法（也可用与子分区定义语法），指定创建N个Hash子分区，同时可选指定Hash子分区的tablespace名列表。
若没有提供子分区模板，同时partition_desc也不指定subpartition_desc，则将创建一个默认子分区。
二级分区的增删改查：对二级分区表插入数据的语法与普通表的语法没有任何差别，当向二级分区表插入数据时，会根据一级分区和二级分区规则，把数据分布到匹配的二级分区中进行存储。
新增一级与二级分区：
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 ...
示例
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;
