gbase8s 为数据表添加新分区

最新推荐文章于 2024-05-15 15:00:42 发布

Tony cui生命不息,学习不止

最新推荐文章于 2024-05-15 15:00:42 发布

阅读量1.1k

点赞数

CC 4.0 BY-SA版权

分类专栏： linux命令文章标签：数据库 sql database

本文链接：https://blog.youkuaiyun.com/qq_35256329/article/details/123368125

linux命令专栏收录该内容

10 篇文章

订阅专栏

这是一个用于GBase8s数据库的shell脚本，主要功能是将名为bmsql_order_line的表重命名为bmsql_order_line_bak，然后创建一个新的分区表t2，采用FRAGMENT BY EXPRESSION进行分区，并将原表数据附加到新分区中。最后，将新表重命名为原表名并添加主键约束。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

gbase8s 为数据表添加新分区

#!/bin/sh
function rename(){
db=$1
dbaccess $db - <<EOF
rename table bmsql_order_line to bmsql_order_line_bak;
drop table if exists t2;
create table t2(
ol_w_id integer not null,
ol_d_id integer not null,
ol_o_id integer not null,
ol_number integer not null,
ol_i_id integer not null,
ol_delivery_d datetime year to second,
ol_amount decimal(6,2),
ol_supply_w_id integer,
ol_quantity integer,
ol_dist_info char(24)
)FRAGMENT BY EXPRESSION
PARTITION p1 ol_w_id<10 and ol_w_id >=1 in dbs3,
PARTITION p2 ol_w_id<20 and ol_w_id >=10 in dbs3,
PARTITION p3 ol_w_id<30 and ol_w_id >=20 in dbs3,
PARTITION p4 ol_w_id<40 and ol_w_id >=30 in dbs3,
PARTITION p5 ol_w_id<50 and ol_w_id >=40 in dbs3,
PARTITION p6 ol_w_id<60 and ol_w_id >=50 in dbs3,
PARTITION p7 ol_w_id<70 and ol_w_id >=60 in dbs3,
PARTITION p8 ol_w_id<80 and ol_w_id >=70 in dbs3,
PARTITION p9 ol_w_id<90 and ol_w_id >=80 in dbs3,
PARTITION p10 ol_w_id<101 and ol_w_id >=90 in dbs3;
alter table bmsql_order_line_bak drop constraint ${delidxname};
alter fragment on table t2 attach bmsql_order_line_bak as partition p11 ol_w_id<111 and ol_w_id >=101;
rename table t2 to bmsql_order_line;
alter table bmsql_order_line add constraint primary key(ol_w_id,ol_d_id,ol_o_id,ol_number) constraint $delidxname;
EOF
}


if [[  -z $GBASEDBTDIR ]];then
	echo "请设置数据库的环境变量 "
	exit 1;
fi

dbname=$1
delidxname="idx_order_line"
if [[ -z $dbname ]];then
	dbname="benchmarksql"
fi
rename $dbname