gbase8s 为数据表添加新分区

最新推荐文章于 2024-04-23 22:08:34 发布

Tony cui生命不息,学习不止

最新推荐文章于 2024-04-23 22:08:34 发布

阅读量886

点赞数

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

本文链接：https://blog.csdn.net/qq_35256329/article/details/123368125

版权

linux命令专栏收录该内容

10 篇文章 0 订阅

订阅专栏

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