活动专区

活动公告

上车！GBase 8a开发者联盟发车了 技术福利、人脉资源全都有！

认证培训专区

认证培训资料

GBase 8s GDCA 认证培训课前准备

GBase 8a

其他

物化视图报错：references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

01-GBase 8s 事务型数据库 安装与卸载

GBase 8s

适配迁移

[GBase 8s 教程]GBase 8s 常用函数、表达式

GBase 8s中的普通表可以通过两种方式转换成分片表：通过init初始化为分片表；通过attach加入新建的分片表。
以下面的测试表t1为例：
```sql
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
```text
### 这里仅输出关心的信息
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
```text
### 这里仅输出关心的信息
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
```text
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
```text
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 8s 普通表转换成分片表的方式分析

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

评论

热门帖子