GBase 8s 普通表转换成分片表的方式分析

GBase 8s中的普通表可以通过两种方式转换成分片表:通过init初始化为分片表;通过attach加入新建的分片表。
以下面的测试表t1为例:

create table t1(col1 int, col2 datetime year to second, col3 varchar(128)) in datadbs01;
create index ix_t1_col1 on t1(col1);

以下将以col2字段按月进行表分片。

1,通过alter fragment on table TABNAME init fragment ... 方式

初始化前表t1的输出oncheck -pt testdb:t1

### 这里仅输出关心的信息
TBLspace Report for testdb:gbasedbt.t1
    Physical Address               6:571
    Creation date                  08/05/2020 13:35:00

          Index ix_t1_col1 fragment partition datadbs01 in DBspace datadbs01
    Physical Address               6:573
    Creation date                  08/05/2020 13:35:01

执行初始化语句,具体的语句为

alter fragment on table t1 init fragment by expression
partition p0 col2 < datetime(2020-09-01 00:00:00) year to second in datadbs01,
partition p1 col2 < datetime(2020-10-01 00:00:00) year to second and col2 >= datetime(2020-09-01 00:00:00) year to second in datadbs02,
partition p2 col2 < datetime(2020-11-01 00:00:00) year to second and col2 >= datetime(2020-10-01 00:00:00) year to second in datadbs03,
partition pr remainder in datadbs04;

再次输出oncheck -pt testdb:t1

### 这里仅输出关心的信息
TBLspace Report for testdb:gbasedbt.t1
          Table fragment partition p0 in DBspace datadbs01
    Physical Address               6:574
    Creation date                  08/05/2020 13:35:41

          Table fragment partition p1 in DBspace datadbs02
    Physical Address               7:7
    Creation date                  08/05/2020 13:35:41

          Table fragment partition p2 in DBspace datadbs03
    Physical Address               8:7
    Creation date                  08/05/2020 13:35:41

          Table fragment partition pr in DBspace datadbs04
    Physical Address               9:7
    Creation date                  08/05/2020 13:35:41

          Index ix_t1_col1 fragment partition datadbs01 in DBspace datadbs01
    Physical Address               6:575
    Creation date                  08/05/2020 13:35:41

结果中显示:原有分区6:571已经变更为6:574,建表的时间也已经改变,表明有数据迁移的情况出现。

2,通过alter fragment on table FRAGMENT_TABNAME attach TABNAME ... 方式

attach操作前表t1的输出oncheck -pt testdb:t1

TBLspace Report for testdb:gbasedbt.t1
    Physical Address               6:571
    Creation date                  08/05/2020 13:36:00

          Index ix_t1_col1 fragment partition datadbs01 in DBspace datadbs01
    Physical Address               6:573
    Creation date                  08/05/2020 13:36:01

执行attach语句,具体的语句为

-- 创建tf分片表,不包含表t1使用的分区
create table tf(col1 int, col2 datetime year to second, col3 varchar(128))
fragment by expression
partition p1 col2 < datetime(2020-10-01 00:00:00) year to second and col2 >= datetime(2020-09-01 00:00:00) year to second in datadbs02,
partition p2 col2 < datetime(2020-11-01 00:00:00) year to second and col2 >= datetime(2020-10-01 00:00:00) year to second in datadbs03,
partition pr remainder in datadbs04;
-- 创建索引,不指定in dbspace
create index ix_tf_col1 on tf(col1);

-- attach语句
ALTER FRAGMENT ON TABLE tf ATTACH t1 AS partition p0 (col2 < datetime(2020-09-01 00:00:00) year to second) before p1;

输出oncheck -pt testdb:tf

TBLspace Report for testdb:gbasedbt.tf
          Table fragment partition p0 in DBspace datadbs01
    Physical Address               6:571
    Creation date                  08/05/2020 13:36:00

          Table fragment partition p1 in DBspace datadbs02
    Physical Address               7:5
    Creation date                  08/05/2020 13:37:23

          Table fragment partition p2 in DBspace datadbs03
    Physical Address               8:5
    Creation date                  08/05/2020 13:37:23

          Table fragment partition pr in DBspace datadbs04
    Physical Address               9:5
    Creation date                  08/05/2020 13:37:23

          Index ix_tf_col1 fragment partition p0 in DBspace datadbs01
    Physical Address               6:573
    Creation date                  08/05/2020 13:36:01

          Index ix_tf_col1 fragment partition p1 in DBspace datadbs02
    Physical Address               7:6
    Creation date                  08/05/2020 13:37:58

          Index ix_tf_col1 fragment partition p2 in DBspace datadbs03
    Physical Address               8:6
    Creation date                  08/05/2020 13:37:58

          Index ix_tf_col1 fragment partition pr in DBspace datadbs04
    Physical Address               9:6
    Creation date                  08/05/2020 13:37:58

结果中显示:原有表t1的分区6:571已经变成为分片表tf的p0分区,该分区的建表时间并未改变。

综上结论:将普通表转换成分片表,最好使用attach方式,以减少数据迁移的影响。

标签: 分片表, GBase, 表分片, attach, alter fragment

评论已关闭