GBase 8a 9.5.3分区的合并和拆分重组功能试用

发表于 作者 laozizhu

GBase 8a的9.5.3版本将支持分区的重组，包括合并多个分区到1个分区，或者将1个分区拆分成多个分区，本文介绍其使用方法。

目前（20220-08-24)该功能【可能】尚未写入产品手册，请使用者查看所用版本的对应产品手册或者厂商联系确认该功能状况。

参数

_t_gcluster_reorganize_partition_enable

该参数以下划线开始，不保证后续该参数依然存在或者名字不会有变化。

默认值0，不支持分区重组。 1：开启分区重组功能

支持session级设置

环境

集群版本

9.5.3.27.14 集群

分区表

create table t_p(a int, b varchar(10))
partition by range(a)(
  partition p0 values less than(10),
  partition p1 values less than(20),
  partition p2 values less than(30),
  partition p3 values less than(40),
  partition p4 values less than(50)
);  

insert into t_p values(1,1111),(10,101010),(15,151515),(20,202020),(25,252525),(30,303030),(40,404040);
select * from t_p partition (p0);
select * from t_p partition (p1);
select * from t_p partition (p2);
select * from t_p partition (p3);
select * from t_p partition (p4);

执行效果，请重点关注p2(2行)和p3(1行)分区，后面将以其为例子进行合并和拆分。

gbase> create table t_p(a int, b varchar(10))
    -> partition by range(a)(
    ->   partition p0 values less than(10),
    ->   partition p1 values less than(20),
    ->   partition p2 values less than(30),
    ->   partition p3 values less than(40),
    ->   partition p4 values less than(50)
    -> );
Query OK, 0 rows affected (Elapsed: 00:00:00.41)

gbase>
gbase> insert into t_p values(1,1111),(10,101010),(15,151515),(20,202020),(25,252525),(30,303030),(40,404040);
Query OK, 7 rows affected (Elapsed: 00:00:00.32)
Records: 7  Duplicates: 0  Warnings: 0

gbase> select * from t_p partition (p0);
+------+------+
| a    | b    |
+------+------+
|    1 | 1111 |
+------+------+
1 row in set (Elapsed: 00:00:00.03)

gbase> select * from t_p partition (p1);
+------+--------+
| a    | b      |
+------+--------+
|   10 | 101010 |
|   15 | 151515 |
+------+--------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> select * from t_p partition (p2);
+------+--------+
| a    | b      |
+------+--------+
|   20 | 202020 |
|   25 | 252525 |
+------+--------+
2 rows in set (Elapsed: 00:00:00.02)

gbase> select * from t_p partition (p3);
+------+--------+
| a    | b      |
+------+--------+
|   30 | 303030 |
+------+--------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from t_p partition (p4);
+------+--------+
| a    | b      |
+------+--------+
|   40 | 404040 |
+------+--------+
1 row in set (Elapsed: 00:00:00.00)

修改参数开启功能

gbase> show variables like '%partition%';
+----------------------------------------------------+-------+
| Variable_name                                      | Value |
+----------------------------------------------------+-------+
| _t_gcluster_hash_redist_on_multiple_olap_partition | 0     |
| _t_gcluster_reorganize_partition_enable            | 0     |
| have_partitioning                                  | YES   |
+----------------------------------------------------+-------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> set _t_gcluster_reorganize_partition_enable=1;
Query OK, 0 rows affected (Elapsed: 00:00:00.00)

合并分区

把分区p2与p3合并为p3分区。此处无需纠结分区名字，叫p3_new等没区别，只要合并后的表分区名字不重复即可。

从执行结果看，p2分区消失，新的p3分区包含了原有p2和p3的数据（2+1=3行)。

gbase> alter table t_p reorganize partition p2,p3 into(partition p3 values less than(40));
Query OK, 0 rows affected (Elapsed: 00:00:00.66)

gbase> show create table t_p;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_p   | CREATE TABLE "t_p" (
  "a" int(11) DEFAULT NULL,
  "b" varchar(10) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace'
 PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (10) TABLESPACE = 'sys_tablespace' ENGINE = EXPRESS,
 PARTITION p1 VALUES LESS THAN (20) TABLESPACE = 'sys_tablespace' ENGINE = EXPRESS,
 PARTITION p3 VALUES LESS THAN (40) TABLESPACE = 'sys_tablespace' ENGINE = EXPRESS,
 PARTITION p4 VALUES LESS THAN (50) TABLESPACE = 'sys_tablespace' ENGINE = EXPRESS) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from t_p partition (p0);
+------+------+
| a    | b    |
+------+------+
|    1 | 1111 |
+------+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from t_p partition (p1);
+------+--------+
| a    | b      |
+------+--------+
|   10 | 101010 |
|   15 | 151515 |
+------+--------+
2 rows in set (Elapsed: 00:00:00.00)

gbase> select * from t_p partition (p3);
+------+--------+
| a    | b      |
+------+--------+
|   20 | 202020 |
|   25 | 252525 |
|   30 | 303030 |
+------+--------+
3 rows in set (Elapsed: 00:00:00.00)

gbase> select * from t_p partition (p4);
+------+--------+
| a    | b      |
+------+--------+
|   40 | 404040 |
+------+--------+
1 row in set (Elapsed: 00:00:00.00)

拆分分区

与合并相反操作，我们将p3分区，拆分成p2和p3，恢复原始的分区属性。请继续忽略分区名字，主要是分区属性。

从操作结果看，拆分后的p2有2行数据，p3有1行数据，与预期相符。

gbase> alter table t_p reorganize partition p3 into(partition p2 values less than(30),partition p3 values less than(40));
Query OK, 0 rows affected (Elapsed: 00:00:01.75)

gbase> show create table t_p;
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_p   | CREATE TABLE "t_p" (
  "a" int(11) DEFAULT NULL,
  "b" varchar(10) DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace'
 PARTITION BY RANGE (a)
(PARTITION p0 VALUES LESS THAN (10) TABLESPACE = 'sys_tablespace' ENGINE = EXPRESS,
 PARTITION p1 VALUES LESS THAN (20) TABLESPACE = 'sys_tablespace' ENGINE = EXPRESS,
 PARTITION p2 VALUES LESS THAN (30) TABLESPACE = 'sys_tablespace' ENGINE = EXPRESS,
 PARTITION p3 VALUES LESS THAN (40) TABLESPACE = 'sys_tablespace' ENGINE = EXPRESS,
 PARTITION p4 VALUES LESS THAN (50) TABLESPACE = 'sys_tablespace' ENGINE = EXPRESS) |
+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from t_p partition (p0);
+------+------+
| a    | b    |
+------+------+
|    1 | 1111 |
+------+------+
1 row in set (Elapsed: 00:00:00.02)

gbase> select * from t_p partition (p1);
+------+--------+
| a    | b      |
+------+--------+
|   10 | 101010 |
|   15 | 151515 |
+------+--------+
2 rows in set (Elapsed: 00:00:00.01)

gbase> select * from t_p partition (p2);
+------+--------+
| a    | b      |
+------+--------+
|   20 | 202020 |
|   25 | 252525 |
+------+--------+
2 rows in set (Elapsed: 00:00:00.01)

gbase> select * from t_p partition (p3);
+------+--------+
| a    | b      |
+------+--------+
|   30 | 303030 |
+------+--------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from t_p partition (p4);
+------+--------+
| a    | b      |
+------+--------+
|   40 | 404040 |
+------+--------+
1 row in set (Elapsed: 00:00:00.01)

注意事项

拆分与合并操作后的分区属性，不能与现有的其它分区属性冲突。

如下以拆分为例，新分区的属性与原有属性冲突的报错信息。

gbase> alter table t_p reorganize partition p3 into(partition p2 values less than(30),partition p3 values less than(50));
ERROR 1702 (HY000): gcluster table error: Reorganize of range partitions cannot change total ranges except for last partition where it can extend the range.
gbase> alter table t_p reorganize partition p3 into(partition p2 values less than(20),partition p3 values less than(40));
ERROR 1702 (HY000): gcluster table error: VALUES LESS THAN value must be strictly increasing for each partition.
gbase>

Post Views: 247

相关文章:

  1. GBase 8a集群支持分区表功能使用样例
  2. GBase 8a 元数据表介绍，分区information_schema.Partitions
  3. GBase 8a分区表计算节点文件名和目录命名规则
  4. GBase 8a分区表指定特定分区的查询方法
  5. GBase 8a数据库Range分区表null值保存分区位置测试
  6. GBase8a MPP Cluster集群状态gcadmin输出说明
  7. GBase8a V862版本节点替换前的准备工作和注意事项
  8. GBase 8a集群查看当前运行状态，内存使用情况
  9. GBase 8a集群查看所有节点正在运行的SQL
  10. GBase 8a集群导出数据带表头字段名