示例

• 重命名分区。

  --创建二级分区表tbl_rge_lst_test。gaussdb=# CREATE TABLE tbl_lst_reg_test(    area_id char(5),    sdate char(8),    eid char(5),    sales_amt int) PARTITION BY LIST(area_id) SUBPARTITION BY RANGE(sdate)(     PARTITION p_1001 VALUES ('1001')(         SUBPARTITION p_1001_201901 VALUES LESS THAN ('20190201'),         SUBPARTITION p_1001_201902 VALUES LESS THAN ('20190301'),         SUBPARTITION p_1001_201903 VALUES LESS THAN ('20190401')     ),    PARTITION p_1002 VALUES ('1002')(         SUBPARTITION p_1002_201901 VALUES LESS THAN ('20190201'),         SUBPARTITION p_1002_201902 VALUES LESS THAN ('20190301'),         SUBPARTITION p_100w VALUES LESS THAN ('20190401')            )  );--将子分区p_100w名称修改为p_1002_201903。gaussdb=# ALTER TABLE tbl_lst_reg_test RENAME SUBPARTITION p_100w TO p_1002_201903;--查询。gaussdb=# SELECT table_name,partition_name,subpartition_name FROM db_tab_subpartitions WHERE table_name = 'tbl_lst_reg_test' AND partition_name = 'p_1002';    table_name    | partition_name | subpartition_name ------------------+----------------+------------------- tbl_lst_reg_test | p_1002         | p_1002_201901 tbl_lst_reg_test | p_1002         | p_1002_201902 tbl_lst_reg_test | p_1002         | p_1002_201903(3 rows)

• 移动分区表空间。

  --创建表空间tbs_data1。gaussdb=# CREATE TABLESPACE tbs_data1 RELATIVE LOCATION 'tablespace/tbs_data1';--移动二级分区p_1002_201901至表空间tbs_data1。gaussdb=# ALTER TABLE tbl_lst_reg_test MOVE SUBPARTITION p_1002_201901 TABLESPACE tbs_data1;--移动二级分区p_1002_201902至表空间tbs_data1。gaussdb=# ALTER TABLE tbl_lst_reg_test MOVE SUBPARTITION FOR ('1002','20190325') TABLESPACE tbs_data1;--查询修改后的二级分区的表空间。gaussdb=# SELECT subpartition_name,tablespace_name FROM db_tab_subpartitions WHERE subpartition_name IN ('p_1002_201901','p_1002_201903'); subpartition_name | tablespace_name -------------------+----------------- p_1002_201901     | tbs_data1 p_1002_201903     | tbs_data1(2 rows)

• 分区交换。

  --创建普通表并插入数据。gaussdb=# CREATE TABLE tbl_test(    area_id char(5),    sdate char(8),    eid char(5),    sales_amt int);gaussdb=# INSERT INTO tbl_test VALUES ('1002','20190326','00001',9000);gaussdb=# INSERT INTO tbl_test VALUES ('1002','20190326','00002',7500);gaussdb=# INSERT INTO tbl_test VALUES ('1002','20190326','00003',6000);--在分区表中插入数据。gaussdb=# INSERT INTO tbl_lst_reg_test VALUES ('1002','20190301','00001',126);--执行分区交换。gaussdb=# ALTER TABLE tbl_lst_reg_test EXCHANGE SUBPARTITION (p_1002_201903) WITH TABLE tbl_test;--查询数据发现表和分区的数据发生了交换。gaussdb=# SELECT * FROM tbl_lst_reg_test; area_id |  sdate   |  eid  | sales_amt ---------+----------+-------+----------- 1002    | 20190326 | 00001 |      9000 1002    | 20190326 | 00002 |      7500 1002    | 20190326 | 00003 |      6000(3 rows)gaussdb=# SELECT * FROM tbl_test; area_id |  sdate   |  eid  | sales_amt ---------+----------+-------+----------- 1002    | 20190301 | 00001 |       126(1 row)--查看分区表空间也发生了交换。gaussdb=# SELECT subpartition_name,tablespace_name FROM db_tab_subpartitions WHERE subpartition_name = 'p_1002_201903'; subpartition_name |  tablespace_name   -------------------+-------------------- p_1002_201903     | DEFAULT TABLESPACE(1 row)

• 分区合并。

  --分区合并。gaussdb=# ALTER TABLE tbl_lst_reg_test MERGE SUBPARTITIONS p_1002_201901,p_1002_201902,p_1002_201903 INTO SUBPARTITION p_1002_20191;--查询二级分区信息。gaussdb=# SELECT table_name,partition_name,subpartition_name FROM db_tab_subpartitions WHERE table_name = 'tbl_lst_reg_test';    table_name    | partition_name | subpartition_name ------------------+----------------+------------------- tbl_lst_reg_test | p_1001         | p_1001_201901 tbl_lst_reg_test | p_1001         | p_1001_201902 tbl_lst_reg_test | p_1001         | p_1001_201903 tbl_lst_reg_test | p_1002         | p_1002_20191(4 rows)

• 添加分区。

  --添加一个一级分区。gaussdb=# ALTER TABLE tbl_lst_reg_test ADD PARTITION p_1003 VALUES('1003') (SUBPARTITION p_1003_201901 VALUES LESS THAN ('20190201')); --在特定的一级分区中添加二级分区。gaussdb=# ALTER TABLE tbl_lst_reg_test MODIFY PARTITION p_1003 ADD SUBPARTITION p_1003_201902 VALUES LESS THAN ('20190301');--查询分区信息。gaussdb=# SELECT table_name,partition_name,subpartition_name FROM db_tab_subpartitions WHERE table_name = 'tbl_lst_reg_test';    table_name    | partition_name | subpartition_name ------------------+----------------+------------------- tbl_lst_reg_test | p_1001         | p_1001_201901 tbl_lst_reg_test | p_1001         | p_1001_201902 tbl_lst_reg_test | p_1001         | p_1001_201903 tbl_lst_reg_test | p_1002         | p_1002_20191 tbl_lst_reg_test | p_1003         | p_1003_201901 tbl_lst_reg_test | p_1003         | p_1003_201902(6 rows)

• 删除分区。

  --删除二级分区p_1003_201902。gaussdb=# ALTER TABLE tbl_lst_reg_test DROP SUBPARTITION p_1003_201902;--查看。gaussdb=# SELECT table_name,partition_name,subpartition_name FROM db_tab_subpartitions WHERE table_name = 'tbl_lst_reg_test' AND partition_name = 'p_1003';    table_name    | partition_name | subpartition_name ------------------+----------------+------------------- tbl_lst_reg_test | p_1003         | p_1003_201901(1 row)--删除一级分区p_1003。gaussdb=# ALTER TABLE tbl_lst_reg_test DROP PARTITION p_1003;--查看。gaussdb=# SELECT table_name,partition_name,subpartition_name FROM db_tab_subpartitions WHERE table_name = 'tbl_lst_reg_test';    table_name    | partition_name | subpartition_name ------------------+----------------+------------------- tbl_lst_reg_test | p_1001         | p_1001_201901 tbl_lst_reg_test | p_1001         | p_1001_201902 tbl_lst_reg_test | p_1001         | p_1001_201903 tbl_lst_reg_test | p_1002         | p_1002_20191(4 rows)

• 切割分区。

  --指定切割点切割分区。gaussdb=# ALTER TABLE tbl_lst_reg_test  SPLIT SUBPARTITION p_1002_20191     AT ('20190201') INTO (SUBPARTITION p_1002_201901,SUBPARTITION p_1002_20191) UPDATE GLOBAL INDEX;--不指定切割垫切割分区。gaussdb=# ALTER TABLE tbl_lst_reg_test SPLIT SUBPARTITION p_1002_20191 INTO (    SUBPARTITION p_1002_201902 VALUES LESS THAN ('20190301'),    SUBPARTITION p_1002_201903) UPDATE GLOBAL INDEX;--查询分区信息。gaussdb=# SELECT table_name,partition_name,subpartition_name,high_value FROM  db_tab_subpartitions;    table_name    | partition_name | subpartition_name | high_value ------------------+----------------+-------------------+------------ tbl_lst_reg_test | p_1001         | p_1001_201901     | 20190201 tbl_lst_reg_test | p_1001         | p_1001_201902     | 20190301 tbl_lst_reg_test | p_1001         | p_1001_201903     | 20190401 tbl_lst_reg_test | p_1002         | p_1002_201901     | 20190201 tbl_lst_reg_test | p_1002         | p_1002_201902     | 20190301 tbl_lst_reg_test | p_1002         | p_1002_201903     | 20190401(6 rows)

• 清空分区数据。

  --清空一级分区。gaussdb=# ALTER TABLE tbl_lst_reg_test TRUNCATE PARTITION p_1001 UPDATE GLOBAL INDEX;--清空二级分区。gaussdb=# ALTER TABLE tbl_lst_reg_test TRUNCATE SUBPARTITION p_1002_201903 UPDATE GLOBAL INDEX;--删除表。gaussdb=# DROP TABLE tbl_lst_reg_test;gaussdb=# DROP TABLE tbl_test;--删除表空间。gaussdb=# DROP TABLESPACE tbs_data1;