GBase数据库表分区内容介绍
一 分区表的种类
cd /opt/dbs
touch dbs1 dbs2 dbs3 dbs4
chmod 660 *
onspaces -c -d dbs1 -p /opt/dbs/dbs1 -o 0 -s 100000 -k 16
onspaces -c -d dbs2 -p /opt/dbs/dbs2 -o 0 -s 100000 -k 16
onspaces -c -d dbs3 -p /opt/dbs/dbs3 -o 0 -s 100000 -k 16
onspaces -c -d dbs4 -p /opt/dbs/dbs4 -o 0 -s 100000 -k 16
1 round-robin 轮转法
create table a11(a int ,b int,c int,d datetime year to second )
fragment by round robin in dbs1,dbs2 ,dbs3,dbs4;
create table a12 (a int ,b int ,c date ,d datetime year to FRACTION(5))
fragment by round robin in dbs1,dbs2,dbs3,dbs4;
create table a13 ( a int ,b int ,c datetime year to fraction(3))
fragment by round robin in dbs1,dbs2,dbs3,dbs4;
–轮转法 ： 唯一索引要指定 空间
create unique index idx_a11_a on a11(a);
872: Invalid fragment strategy or expression for the unique index.
create unique index idx_a11_a on a11(a) in datadbs1;
create index idx_a11_b on a11(b);
insert into a11 values(1,1,1,current);
insert into a11 values(2,2,2,current);
insert into a11 values(3,3,3,current);
insert into a11 values(4,4,4,current);
[gbasedbt@node1 ~]$ oncheck -ci test1:a11
Validating indexes for test1:gbasedbt.a11…
Index idx_a11_b
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index fragment partition dbs4 in DBspace dbs4
Index idx_a11_a
Index fragment partition datadbs1 in DBspace datadbs1
2 表达式
create table b11 (a int ,b int ,c date ,d datetime year to FRACTION(5))
fragment by expression
d<‘2011-01-01 00:00:00’ and d>=‘2010-01-01 00:00:00’ in dbs1,
d<‘2012-01-01 00:00:00’ and d>=‘2011-01-01 00:00:00’ in dbs2,
d<‘2013-01-01 00:00:00’ and d>=‘2012-01-01 00:00:00’ in dbs3;
create unique index idx_b1_a on b11(a);
872: Invalid fragment strategy or expression for the unique index.
create unique index idx_b1_a on b11(a) in datadbs1;
create index idx_b1_b on b11(b);
create index idx_b1_d on b11(d);
[gbasedbt@node1 ~]$ oncheck -ci test1:b11
Validating indexes for test1:gbasedbt.b11…
Index idx_b1_a
Index fragment partition datadbs1 in DBspace datadbs1
Index idx_b1_b
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index idx_b1_d
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
drop index idx_b1_d;
create index idx_b1_d on b11(d)
fragment by expression
d<‘2011-01-01 00:00:00’ and d>=‘2010-01-01 00:00:00’ in dbs1,
d<‘2012-01-01 00:00:00’ and d>=‘2011-01-01 00:00:00’ in dbs2,
d<‘2013-01-01 00:00:00’ and d>=‘2012-01-01 00:00:00’ in dbs3;
[gbasedbt@node1 ~]$ oncheck -ci test1:b11
Validating indexes for test1:gbasedbt.b11…
Index idx_b1_a
Index fragment partition datadbs1 in DBspace datadbs1
Index idx_b1_b
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index idx_b1_d
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
drop index idx_b1_a;
create unique index idx_b1_a on b11(a,d);
[gbasedbt@node1 ~]$ oncheck -ci test1:b11
Validating indexes for test1:gbasedbt.b11…
Index idx_b1_a
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index idx_b1_b
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index idx_b1_d
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
3 remainder
create table b12 (a int ,b int ,c date ,d datetime year to FRACTION(5))
fragment by expression
d<‘2011-01-01 00:00:00’ and d>=‘2010-01-01 00:00:00’ in dbs1,
d<‘2012-01-01 00:00:00’ and d>=‘2011-01-01 00:00:00’ in dbs2,
remainder in dbs3;
create index idx_b12_a on b12(a);
create index idx_b12_d on b12(d);
[gbasedbt@node1 ~]$ oncheck -ci test1:b12
Validating indexes for test1:gbasedbt.b12…
Index idx_b12_a
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index idx_b12_d
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
4 列表 LIST
create table b13(a int ,b int ,c int)
fragment by list©
partition p0 values(1,2) in dbs1,
partition p1 values(3,4) in dbs2,
partition p2 values(5) in dbs3;
create table b14(a int ,b int ,c int)
fragment by list©
partition p0 values(1,2) in dbs1,
partition p1 values(3,4) in dbs2,
partition p2 values(null) in dbs3;
5 固定间隔 INTERVAL
create table c11( a int ,b int ,c int ,d datetime year to fraction(5))
fragment by range (d)
interval (10 units day)
store in (dbs1,dbs2,dbs3)
partition p1 values<‘2013-01-01 00:00:00’ in datadbs1;
create unique index idx_c11_a on c11(a);
872: Invalid fragment strategy or expression for the unique index.
create unique index idx_c11_a on c11(a) in datadbs2;
drop index idx_c11_a;
create unique index idx_c11_a on c11(a,d) ;
[gbasedbt@node1 ~]$ oncheck -ci test1:c11
Validating indexes for test1:gbasedbt.c11…
Index idx_c11_a
Index fragment partition p1 in DBspace datadbs1
[gbasedbt@node1 ~]$
create table c12( a int ,b int ,c int ,d datetime year to fraction(5))
fragment by range (d)
interval (10 units day)
store in (dbs1,dbs2,dbs3)
partition p1 values<‘2013-01-01 00:00:00’ in datadbs1;
create table c13(a int ,b int,c int)
fragment by range (b)
interval (100)
store in (dbs1,dbs2,dbs3)
partition p0 values<200 in datadbs1,
partition p1 values<400 in datadbs2;
create index idx_c13_a on c13(a);
insert into c13 values(1,1,1);
insert into c13 values(2,2,2);
insert into c13 values(101,101,1);
insert into c13 values(102,102,2);
insert into c13 values(103,103,4);
insert into c13 values(201,201,1);
insert into c13 values(202,202,2);
insert into c13 values(203,203,4);
insert into c13 values(401,401,1);
insert into c13 values(402,402,2);
insert into c13 values(403,403,4);
[gbasedbt@node1 ~]$ oncheck -ci test1:c13
Validating indexes for test1:gbasedbt.c13…
Index idx_c13_a
Index fragment partition p0 in DBspace datadbs1
Index fragment partition p1 in DBspace datadbs2
Index fragment partition sys_p2 in DBspace dbs1
create table c14(a int ,b int,c int)
fragment by range (b)
interval (100)
store in (dbs1,dbs2,dbs3)
partition p0 values<200 in datadbs1,
partition p1 values<400 in datadbs2,
partition p2 values<600 in datadbs3;
create index idx_c14_a on c14(a);
insert into c14 values(1,1,1);
insert into c14 values(2,2,2);
insert into c14 values(101,101,1);
insert into c14 values(102,102,2);
insert into c14 values(103,103,4);
insert into c14 values(201,201,1);
insert into c14 values(202,202,2);
insert into c14 values(203,203,4);
insert into c14 values(401,401,1);
insert into c14 values(402,402,2);
insert into c14 values(403,403,4);
[gbasedbt@node1 ~]$ oncheck -ci test1:c14
Validating indexes for test1:gbasedbt.c14…
Index idx_c14_a
Index fragment partition p0 in DBspace datadbs1
Index fragment partition p1 in DBspace datadbs2
Index fragment partition p2 in DBspace datadbs3
[gbasedbt@node1 ~]$ oncheck -pt test1:c13|grep DBspace
Table fragment partition p0 in DBspace datadbs1
Table fragment partition p1 in DBspace datadbs2
Table fragment partition sys_p2 in DBspace dbs1
Index idx_c13_a fragment partition p0 in DBspace datadbs1
Index idx_c13_a fragment partition p1 in DBspace datadbs2
Index idx_c13_a fragment partition sys_p2 in DBspace dbs1
二 分区表的管理
1 INIT 将表改为分区表
注： 如果表还有索引,索引的空间未指定的话，INIT 后索引存放在 ROOTDBS
create table d0( a int ,b int ,c int);
insert into d0 values(1,1,1);
insert into d0 values(2,1,1);
insert into d0 values(11,1,2);
insert into d0 values(12,1,2);
insert into d0 values(21,1,3);
insert into d0 values(22,1,3);
insert into d0 values(23,1,4);
alter fragment on table d0 init fragment by round robin in dbs1,dbs2,dbs3;
create table d1( a int ,b int ,c int);
insert into d1 values(1,1,1);
insert into d1 values(2,1,1);
insert into d1 values(11,1,2);
insert into d1 values(12,1,2);
insert into d1 values(21,1,3);
insert into d1 values(22,1,3);
insert into d1 values(23,1,4);
alter fragment on table d1 init fragment by expression
c<3 and c>=0 in dbs1,
c<10 and c>=3 in dbs2;
drop table d1;
create table d1( a int ,b int ,c int);
insert into d1 values(1,1,1);
insert into d1 values(2,1,1);
insert into d1 values(11,1,2);
insert into d1 values(12,1,2);
insert into d1 values(21,1,3);
insert into d1 values(22,1,3);
insert into d1 values(23,1,4);
create unique index idx_d1 on d1(a);
alter fragment on table d1 init fragment by expression
c<3 and c>=0 in dbs1,
c<10 and c>=3 in dbs2;
[gbasedbt@node1 ~]$ oncheck -ci test1:d1
Validating indexes for test1:gbasedbt.d1…
Index idx_d1
Index fragment partition rootdbs in DBspace rootdbs
2 ADD ,DROP
create table d16 (a int ,b int ,c date ,d datetime year to FRACTION(5))
fragment by expression
partition p1 d<‘2011-01-01 00:00:00’ and d>=‘2010-01-01 00:00:00’ in dbs1,
partition p2 d<‘2012-01-01 00:00:00’ and d>=‘2011-01-01 00:00:00’ in dbs2;
alter FRAGMENT ON TABLE d16 add partition p3 d<‘2013-01-01 00:00:00’ and d>=‘2012-01-01 00:00:00’ in dbs2;
alter FRAGMENT ON TABLE d16 add partition p4 d<‘2014-01-01 00:00:00’ and d>=‘2013-01-01 00:00:00’ in dbs3;
alter fragment on table d16 add partition p5 d<‘2015-01-01 00:00:00’ and d>=‘2014-01-01 00:00:00’ in datadbs1;
[gbasedbt@node1 ~]$ dbschema -d test1 -t d16 -ss
DBSCHEMA Schema Utility GBASE-SQL Version 12.10.FC4G1AEE
{ TABLE “gbasedbt”.d16 row size = 23 number of columns = 4 index size = 0 }
create table “gbasedbt”.d16
(
a integer,
b integer,
c date,
d datetime year to fraction(5)
)
fragment by expression
partition p1 ((d < datetime(2011-01-01 00:00:00) year to
fraction(5) ) AND (d >= datetime(2010-01-01 00:00:00)
year to fraction(5) ) ) in dbs1,
partition p2 ((d < datetime(2012-01-01 00:00:00) year to
fraction(5) ) AND (d >= datetime(2011-01-01 00:00:00)
year to fraction(5) ) ) in dbs2,
partition p3 ((d < datetime(2013-01-01 00:00:00) year to
fraction(5) ) AND (d >= datetime(2012-01-01 00:00:00)
year to fraction(5) ) ) in dbs2,
partition p5 ((d < datetime(2015-01-01 00:00:00) year to
fraction(5) ) AND (d >= datetime(2014-01-01 00:00:00)
year to fraction(5) ) ) in datadbs1
extent size 16 next size 64 lock mode row;
revoke all on “gbasedbt”.d16 from “public” as “gbasedbt”;
alter fragment on table d16 drop partition p5;
[gbasedbt@node1 ~]$ dbschema -d test1 -t d16 -ss
DBSCHEMA Schema Utility GBASE-SQL Version 12.10.FC4G1AEE
{ TABLE “gbasedbt”.d16 row size = 23 number of columns = 4 index size = 0 }
create table “gbasedbt”.d16
(
a integer,
b integer,
c date,
d datetime year to fraction(5)
)
fragment by expression
partition p1 ((d < datetime(2011-01-01 00:00:00) year to
fraction(5) ) AND (d >= datetime(2010-01-01 00:00:00)
year to fraction(5) ) ) in dbs1,
partition p2 ((d < datetime(2012-01-01 00:00:00) year to
fraction(5) ) AND (d >= datetime(2011-01-01 00:00:00)
year to fraction(5) ) ) in dbs2,
partition p3 ((d < datetime(2013-01-01 00:00:00) year to
fraction(5) ) AND (d >= datetime(2012-01-01 00:00:00)
year to fraction(5) ) ) in dbs2
extent size 16 next size 64 lock mode row;
revoke all on “gbasedbt”.d16 from “public” as “gbasedbt”;
3 MODIFY
alter fragment on table d16 modify partition p3 to partition p3 d<‘2016-01-01 00:00:00’ and d>=‘2014-01-01 00:00:00’ in datadbs1;
fragment by expression
partition p1 ((d < datetime(2011-01-01 00:00:00) year to
fraction(5) ) AND (d >= datetime(2010-01-01 00:00:00)
year to fraction(5) ) ) in dbs1,
partition p2 ((d < datetime(2012-01-01 00:00:00) year to
fraction(5) ) AND (d >= datetime(2011-01-01 00:00:00)
year to fraction(5) ) ) in dbs2,
partition p3 ((d < datetime(2016-01-01 00:00:00) year to
fraction(5) ) AND (d >= datetime(2014-01-01 00:00:00)
year to fraction(5) ) ) in datadbs1
extent size 16 next size 64 lock mode row;
revoke all on “gbasedbt”.d16 from “public” as “gbasedbt”;
4 ATTACH attach 后 ，ATTACH 的表被删除
create table h1(a int ,b int,c int);
insert into h1 values(1,1,1);
insert into h1 values(2,1,1);
insert into h1 values(11,1,2);
insert into h1 values(12,1,2);
insert into h1 values(21,1,3);
insert into h1 values(22,1,4);
insert into h1 values(23,1,3);
alter fragment on table h1 init
fragment by list ©
partition p1 values(1) in dbs1,
partition p2 values(2) in dbs2,
partition p3 values(3) in dbs3,
partition p4 values(4) in datadbs1;
create table h11(a int ,b int,c int) in dbs1;
insert into h11 values(1111,1,5);
insert into h11 values(1112,1,5);
alter fragment on table h1 attach h11 as partition p5 values(5) ;
[gbasedbt@node1 ~]$ dbschema -d test1 -t h1 -ss
DBSCHEMA Schema Utility GBASE-SQL Version 12.10.FC4G1AEE
{ TABLE “gbasedbt”.h1 row size = 12 number of columns = 3 index size = 0 }
create table “gbasedbt”.h1
(
a integer,
b integer,
c integer
)
fragment by list©
partition p1 VALUES (1 ) in dbs1 ,
partition p2 VALUES (2 ) in dbs2,
partition p3 VALUES (3 ) in dbs3,
partition p4 VALUES (4 ) in datadbs1,
partition p5 VALUES (5 ) in dbs1
extent size 16 next size 16 lock mode row;
revoke all on “gbasedbt”.h1 from “public” as “gbasedbt”;
create table h111(a int ,b int,c int) in dbs1;
insert into h111 values(1111,1,6);
insert into h111 values(1112,1,6);
alter fragment on table h1 attach h111 as partition p11 values(6) ;
5 detach detach 的表自动创建
alter fragment on table h1 detach partition p1 h11 ;
alter fragment on table h1 detach partition p2 h12 ;
> select * from h1;
a b c
21 1 3
23 1 3
22 1 4
1111 1 5
1112 1 5
1111 1 6
1112 1 6
7 row(s) retrieved.
select * from h11;
a b c
1 1 1
2 1 1
2 row(s) retrieved.
select * from h12;
a b c
11 1 2
12 1 2
2 row(s) retrieved.
execute function task(‘table estimate_compression’,‘t1’,‘test’,‘gbasedbt’);
(expression) est curr change partnum coloff table/index
Succeeded: tabl
e estimate_compression test:gbasedbt.t1
1 row(s) retrieved.
execute function task(‘table create_dictionary’,‘t1’,‘test’,‘gbasedbt’);
(expression) Succeeded: table create_dictionary test:gbasedbt.t1
1 row(s) retrieved
execute function task(‘table repack’,‘t1’,‘test’,‘gbasedbt’);
(expression) Succeeded: table repack test:gbasedbt.t1
1 row(s) retrieved.
execute function task(‘table shrink’,‘t1’,‘test’,‘gbasedbt’);
(expression) Succeeded: table shrink test:gbasedbt.t1
1 row(s) retrieved.
execute function task(‘table uncompress’,‘t1’,‘test’,‘gbasedbt’);
(expression) Succeeded: table uncompress test:gbasedbt.t1
1 row(s) retrieved.
[gbasedbt@node1 ~]$ onbar -b -L 0
/home/gbasedbt/gbase/bin/onbar: line 89: 4728 Segmentation fault ${gbasedbtDIR}/bin/onbar_d “$@”
//
三 使用存储过程将表改为分区表（表中有无数据都可）
//
1 创建相应 DBSPACE
cd /u02/gbase/dbs
touch scdhis_2013 scdhis_2014 scdhis_2015 scdhis_2016 scdhis_2017 scdhis_2018 scdhis_2019 scdhis_2020 scdhis_2021 scdhis_2022 maxu
chmod 660 *
onspaces -c -d scdhis_2013 -p /u02/gbase/dbs/scdhis_2013 -o 0 -s 20000 -k 16
onspaces -c -d scdhis_2014 -p /u02/gbase/dbs/scdhis_2014 -o 0 -s 20000 -k 16
onspaces -c -d scdhis_2015 -p /u02/gbase/dbs/scdhis_2015 -o 0 -s 20000 -k 16
onspaces -c -d scdhis_2016 -p /u02/gbase/dbs/scdhis_2016 -o 0 -s 20000 -k 16
onspaces -c -d scdhis_2017 -p /u02/gbase/dbs/scdhis_2017 -o 0 -s 20000 -k 16
onspaces -c -d scdhis_2018 -p /u02/gbase/dbs/scdhis_2018 -o 0 -s 20000 -k 16
onspaces -c -d scdhis_2019 -p /u02/gbase/dbs/scdhis_2019 -o 0 -s 20000 -k 16
onspaces -c -d scdhis_2020 -p /u02/gbase/dbs/scdhis_2020 -o 0 -s 20000 -k 16
onspaces -c -d scdhis_2021 -p /u02/gbase/dbs/scdhis_2021 -o 0 -s 20000 -k 16
onspaces -c -d scdhis_2022 -p /u02/gbase/dbs/scdhis_2022 -o 0 -s 20000 -k 16
onspaces -c -d maxu -p /u02/gbase/dbs/maxu -o 0 -s 20000 -k 16
2 将表改为range分区表
drop table his_smpl_dat_min_1;
CREATE TABLE his_smpl_dat_min_1(
TIME_DAT DATETIME YEAR TO FRACTION (5) NOT NULL,
VAL001_DAT DECIMAL(20,5) DEFAULT 0 NOT NULL,
QUA001_DAT SMALLINT DEFAULT 0 NOT NULL
);
alter fragment on table his_smpl_dat_min_1 init fragment by expression
partition SCDHIS_2013 TIME_DAT<‘2014-01-01 00:00:00’ and TIME_DAT>=‘2013-01-01 00:00:00’ in scdhis_2013,
partition SCDHIS_2014 TIME_DAT<‘2015-01-01 00:00:00’ and TIME_DAT>=‘2014-01-01 00:00:00’ in scdhis_2014,
partition SCDHIS_2015 TIME_DAT<‘2016-01-01 00:00:00’ and TIME_DAT>=‘2015-01-01 00:00:00’ in scdhis_2015,
partition SCDHIS_2016 TIME_DAT<‘2017-01-01 00:00:00’ and TIME_DAT>=‘2016-01-01 00:00:00’ in scdhis_2016,
partition SCDHIS_2017 TIME_DAT<‘2018-01-01 00:00:00’ and TIME_DAT>=‘2017-01-01 00:00:00’ in scdhis_2017,
partition SCDHIS_2018 TIME_DAT<‘2019-01-01 00:00:00’ and TIME_DAT>=‘2018-01-01 00:00:00’ in scdhis_2018,
partition SCDHIS_2019 TIME_DAT<‘2020-01-01 00:00:00’ and TIME_DAT>=‘2019-01-01 00:00:00’ in scdhis_2019,
partition SCDHIS_2020 TIME_DAT<‘2021-01-01 00:00:00’ and TIME_DAT>=‘2020-01-01 00:00:00’ in scdhis_2020,
partition SCDHIS_2021 TIME_DAT<‘2022-01-01 00:00:00’ and TIME_DAT>=‘2021-01-01 00:00:00’ in scdhis_2021,
partition SCDHIS_2022 TIME_DAT<‘2023-01-01 00:00:00’ and TIME_DAT>=‘2022-01-01 00:00:00’ in scdhis_2022;
[gbasedbt@node1 ~]$ dbschema -d ma2 -t his_smpl_dat_min_1 -ss
create table “gbasedbt”.his_smpl_dat_min_1
(
time_dat datetime year to fraction(5) not null ,
val001_dat decimal(20,5)
default 0 not null ,
qua001_dat smallint
default 0 not null
)
fragment by expression
((time_dat < datetime(2014-01-01 00:00:00) year to fraction(5)
) AND (time_dat >= datetime(2013-01-01 00:00:00)
year to fraction(5) ) ) in scdhis_2013,
((time_dat < datetime(2015-01-01 00:00:00) year to fraction(5)
) AND (time_dat >= datetime(2014-01-01 00:00:00)
year to fraction(5) ) ) in scdhis_2014,
((time_dat < datetime(2016-01-01 00:00:00) year to fraction(5)
) AND (time_dat >= datetime(2015-01-01 00:00:00)
year to fraction(5) ) ) in scdhis_2015,
((time_dat < datetime(2017-01-01 00:00:00) year to fraction(5)
) AND (time_dat >= datetime(2016-01-01 00:00:00)
year to fraction(5) ) ) in scdhis_2016,
((time_dat < datetime(2018-01-01 00:00:00) year to fraction(5)
) AND (time_dat >= datetime(2017-01-01 00:00:00)
year to fraction(5) ) ) in scdhis_2017,
((time_dat < datetime(2019-01-01 00:00:00) year to fraction(5)
) AND (time_dat >= datetime(2018-01-01 00:00:00)
year to fraction(5) ) ) in scdhis_2018,
((time_dat < datetime(2020-01-01 00:00:00) year to fraction(5)
) AND (time_dat >= datetime(2019-01-01 00:00:00)
year to fraction(5) ) ) in scdhis_2019,
((time_dat < datetime(2021-01-01 00:00:00) year to fraction(5)
) AND (time_dat >= datetime(2020-01-01 00:00:00)
year to fraction(5) ) ) in scdhis_2020,
((time_dat < datetime(2022-01-01 00:00:00) year to fraction(5)
) AND (time_dat >= datetime(2021-01-01 00:00:00)
year to fraction(5) ) ) in scdhis_2021,
((time_dat < datetime(2023-01-01 00:00:00) year to fraction(5)
) AND (time_dat >= datetime(2022-01-01 00:00:00)
year to fraction(5) ) ) in scdhis_2022
extent size 16 next size 64 lock mode page;
3 创建存储过程将表改为 expression 分区表
drop procedure if exists alter_table_fragment;
create procedure alter_table_fragment()
define v_sql lvarchar(32739);
define v_tabname VARCHAR(128);
SET DEBUG FILE TO ‘/home/gbasedbt/info_debug.txt’;
TRACE ‘BEGIN TRACE’ ;
trace on;
FOREACH select tabname into v_tabname from systables where tabname like ‘his_smpl_dat_min_%’
let v_sql=‘alter fragment on table ‘|| v_tabname ||’ init fragment by expression ‘||
’ partition SCDHIS_2013 TIME_DAT<’‘2014-01-01 00:00:00’’ and TIME_DAT>=’‘2013-01-01 00:00:00’’ in scdhis_2013,’||
’ partition SCDHIS_2014 TIME_DAT<’‘2015-01-01 00:00:00’’ and TIME_DAT>=’‘2014-01-01 00:00:00’’ in scdhis_2014,’||
’ partition SCDHIS_2015 TIME_DAT<’‘2016-01-01 00:00:00’’ and TIME_DAT>=’‘2015-01-01 00:00:00’’ in scdhis_2015,’||
’ partition SCDHIS_2016 TIME_DAT<’‘2017-01-01 00:00:00’’ and TIME_DAT>=’‘2016-01-01 00:00:00’’ in scdhis_2016,’||
’ partition SCDHIS_2017 TIME_DAT<’‘2018-01-01 00:00:00’’ and TIME_DAT>=’‘2017-01-01 00:00:00’’ in scdhis_2017,’||
’ partition SCDHIS_2018 TIME_DAT<’‘2019-01-01 00:00:00’’ and TIME_DAT>=’‘2018-01-01 00:00:00’’ in scdhis_2018,’||
’ partition SCDHIS_2019 TIME_DAT<’‘2020-01-01 00:00:00’’ and TIME_DAT>=’‘2019-01-01 00:00:00’’ in scdhis_2019,’||
’ partition SCDHIS_2020 TIME_DAT<’‘2021-01-01 00:00:00’’ and TIME_DAT>=’‘2020-01-01 00:00:00’’ in scdhis_2020,’||
’ partition SCDHIS_2021 TIME_DAT<’‘2022-01-01 00:00:00’’ and TIME_DAT>=’‘2021-01-01 00:00:00’’ in scdhis_2021,’||
’ partition SCDHIS_2022 TIME_DAT<’‘2023-01-01 00:00:00’’ and TIME_DAT>=’‘2022-01-01 00:00:00’’ in scdhis_2022’;
EXECUTE IMMEDIATE v_sql;
END FOREACH
trace off;
end procedure;
–测试
drop table his_smpl_dat_min_1;
CREATE TABLE his_smpl_dat_min_1(
TIME_DAT DATETIME YEAR TO FRACTION (5) NOT NULL,
VAL001_DAT DECIMAL(20,5) DEFAULT 0 NOT NULL,
QUA001_DAT SMALLINT DEFAULT 0 NOT NULL
);
execute PROCEDURE alter_table_fragment();
select s1.partn,s1.partition,s1.exprtext ,s1.nrows from SYSFRAGMENTS s1,systables s2
where s1.tabid=s2.tabid and s1.tabid>100 and s2.tabname =‘his_smpl_dat_min_1’; >
partn 10485964
partition scdhis_2013
exprtext
((time_dat < datetime(2014-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2013-01-01 00:00:00) year to fraction(5) ) )
nrows 0.00
partn 11534539
partition scdhis_2014
exprtext
((time_dat < datetime(2015-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2014-01-01 00:00:00) year to fraction(5) ) )
nrows 0.00
partn 12582914
partition scdhis_2015
exprtext
((time_dat < datetime(2016-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2015-01-01 00:00:00) year to fraction(5) ) )
nrows 0.00
partn 13631490
partition scdhis_2016
exprtext
((time_dat < datetime(2017-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2016-01-01 00:00:00) year to fraction(5) ) )
nrows 0.00
partn 14680267
partition scdhis_2017
exprtext
((time_dat < datetime(2018-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2017-01-01 00:00:00) year to fraction(5) ) )
nrows 0.00
partn 15728843
partition scdhis_2018
exprtext
((time_dat < datetime(2019-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2018-01-01 00:00:00) year to fraction(5) ) )
nrows 0.00
partn 16777419
partition scdhis_2019
exprtext
((time_dat < datetime(2020-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2019-01-01 00:00:00) year to fraction(5) ) )
nrows 0.00
partn 17825995
partition scdhis_2020
exprtext
((time_dat < datetime(2021-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2020-01-01 00:00:00) year to fraction(5) ) )
nrows 0.00
partn 18874571
partition scdhis_2021
exprtext
((time_dat < datetime(2022-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2021-01-01 00:00:00) year to fraction(5) ) )
nrows 0.00
partn 19923147
partition scdhis_2022
exprtext
((time_dat < datetime(2023-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2022-01-01 00:00:00) year to fraction(5) ) )
nrows 0.00
10 row(s) retrieved.
–插入数据
insert into his_smpl_dat_min_1 values(current,1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,1),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,10),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,20),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,30),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,40),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,45),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,50),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,60),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,-10),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,-40),1,1);
–更新统计
update statistics;
–分区信息
select s1.partn,s1.partition,s1.exprtext ,s1.nrows from SYSFRAGMENTS s1,systables s2
where s1.tabid=s2.tabid and s1.tabid>100 and s2.tabname =‘his_smpl_dat_min_1’; >
partn 10485964
partition scdhis_2013
exprtext
((time_dat < datetime(2014-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2013-01-01 00:00:00) year to fraction(5) ) )
nrows 0.00
partn 11534539
partition scdhis_2014
exprtext
((time_dat < datetime(2015-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2014-01-01 00:00:00) year to fraction(5) ) )
nrows 1.000000000000
partn 12582914
partition scdhis_2015
exprtext
((time_dat < datetime(2016-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2015-01-01 00:00:00) year to fraction(5) ) )
nrows 0.00
partn 13631490
partition scdhis_2016
exprtext
((time_dat < datetime(2017-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2016-01-01 00:00:00) year to fraction(5) ) )
nrows 1.000000000000
partn 14680267
partition scdhis_2017
exprtext
((time_dat < datetime(2018-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2017-01-01 00:00:00) year to fraction(5) ) )
nrows 2.000000000000
partn 15728843
partition scdhis_2018
exprtext
((time_dat < datetime(2019-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2018-01-01 00:00:00) year to fraction(5) ) )
nrows 1.000000000000
partn 16777419
partition scdhis_2019
exprtext
((time_dat < datetime(2020-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2019-01-01 00:00:00) year to fraction(5) ) )
nrows 2.000000000000
partn 17825995
partition scdhis_2020
exprtext
((time_dat < datetime(2021-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2020-01-01 00:00:00) year to fraction(5) ) )
nrows 1.000000000000
partn 18874571
partition scdhis_2021
exprtext
((time_dat < datetime(2022-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2021-01-01 00:00:00) year to fraction(5) ) )
nrows 2.000000000000
partn 19923147
partition scdhis_2022
exprtext
((time_dat < datetime(2023-01-01 00:00:00) year to fraction(5) ) AND (time_dat >= datetime(2022-01-01 00:00:00) year to fraction(5) ) )
nrows 1.000000000000
10 row(s) retrieved.
4 将表改为interval分区表
drop table his_smpl_dat_min_1;
CREATE TABLE his_smpl_dat_min_1(
TIME_DAT DATETIME YEAR TO FRACTION (5) NOT NULL,
VAL001_DAT DECIMAL(20,5) DEFAULT 0 NOT NULL,
QUA001_DAT SMALLINT DEFAULT 0 NOT NULL
);
alter fragment on table his_smpl_dat_min_1 init fragment by range(TIME_DAT)
interval (1 units year)
store in (SCDHIS_2013,SCDHIS_2014,SCDHIS_2015,SCDHIS_2016,SCDHIS_2017,SCDHIS_2018,SCDHIS_2019,SCDHIS_2020,SCDHIS_2021,SCDHIS_2022)
partition p1 values<‘2014-01-01 00:00:00’ in SCDHIS_2013;
[gbasedbt@node1 ~]$ dbschema -d ma2 -t his_smpl_dat_min_1 -ss
create table “gbasedbt”.his_smpl_dat_min_1
(
time_dat datetime year to fraction(5) not null ,
val001_dat decimal(20,5)
default 0 not null ,
qua001_dat smallint
default 0 not null
)
fragment by range(time_dat) interval(interval( 1) year(9) to year) store in(scdhis_2013,scdhis_2014,scdhis_2015,scdhis_2016,scdhis_2017,scdhis_2018,scdhis_2019,scdhis_2020,scdhis_2021,scdhis_2022)
partition p1 VALUES < datetime(2014-01-01 00:00:00) year to fraction(5) in scdhis_2013
extent size 16 next size 64 lock mode page;
5 创建存储过程将表改为 interval 分区表
drop procedure if exists alter_table_fragment;
create procedure alter_table_fragment()
define v_sql lvarchar(32739);
define v_tabname VARCHAR(128);
SET DEBUG FILE TO ‘/home/gbasedbt/info_debug.txt’;
TRACE ‘BEGIN TRACE’ ;
trace on;
FOREACH select tabname into v_tabname from systables where tabname like ‘his_smpl_dat_min_%’
let v_sql=‘alter fragment on table ‘|| v_tabname ||’ init fragment by range(TIME_DAT)’||
’ interval (1 units year)’||
’ store in (SCDHIS_2013,SCDHIS_2014,SCDHIS_2015,SCDHIS_2016,SCDHIS_2017,SCDHIS_2018,SCDHIS_2019,SCDHIS_2020,SCDHIS_2021,SCDHIS_2022)’||
’ partition p1 values<’‘2014-01-01 00:00:00’’ in SCDHIS_2013’;
EXECUTE IMMEDIATE v_sql;
END FOREACH
trace off;
end procedure;
–测试
drop table his_smpl_dat_min_1;
CREATE TABLE his_smpl_dat_min_1(
TIME_DAT DATETIME YEAR TO FRACTION (5) NOT NULL,
VAL001_DAT DECIMAL(20,5) DEFAULT 0 NOT NULL,
QUA001_DAT SMALLINT DEFAULT 0 NOT NULL
);
execute PROCEDURE alter_table_fragment();
select s1.partn,s1.partition,s1.exprtext ,s1.nrows from SYSFRAGMENTS s1,systables s2
where s1.tabid=s2.tabid and s1.tabid>100 and s2.tabname =‘his_smpl_dat_min_1’;
partn 0
partition
exprtext
time_dat
nrows 0.00
partn 0
partition
exprtext
interval( 1) year(9) to year
nrows 0.00
partn 0
partition
exprtext
scdhis_2013,scdhis_2014,scdhis_2015,scdhis_2016,scdhis_2017,scdhis_2018,scdhis_2019,scdhis_2020,scdhis_2021,scdhis_2022
nrows 0.00
partn 10485964
partition p1
exprtext
VALUES < datetime(2014-01-01 00:00:00) year to fraction(5)
nrows 0.00
4 row(s) retrieved.
–插入数据
truncate table his_smpl_dat_min_1;
insert into his_smpl_dat_min_1 values(current,1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,1),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,10),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,20),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,30),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,40),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,45),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,50),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,60),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,-10),1,1);
insert into his_smpl_dat_min_1 values(ADD_MONTHS(current,-40),1,1);
update statistics;
select s1.partn,s1.partition,s1.exprtext ,s1.nrows from SYSFRAGMENTS s1,systables s2
where s1.tabid=s2.tabid and s1.tabid>100 and s2.tabname =‘his_smpl_dat_min_1’;
partn 0
partition
exprtext
time_dat
nrows 0.00
partn 0
partition
exprtext
interval( 1) year(9) to year
nrows 0.00
partn 0
partition
exprtext
scdhis_2013,scdhis_2014,scdhis_2015,scdhis_2016,scdhis_2017,scdhis_2018,scdhis_2019,scdhis_2020,scdhis_2021,scdhis_2022
nrows 0.00
partn 10485964
partition p1
exprtext
VALUES < datetime(2014-01-01 00:00:00) year to fraction(5)
nrows 0.00
partn 16777419
partition sys_p1
exprtext
VALUES >= datetime(2014-01-01 00:00:00) year to fraction(5) AND VALUES < datetime(2015-01-01 00:00:00) year to fraction(5)
nrows 1.000000000000
partn 15728843
partition sys_p3
exprtext
VALUES >= datetime(2016-01-01 00:00:00) year to fraction(5) AND VALUES < datetime(2017-01-01 00:00:00) year to fraction(5)
nrows 1.000000000000
partn 19923147
partition sys_p4
exprtext
VALUES >= datetime(2017-01-01 00:00:00) year to fraction(5) AND VALUES < datetime(2018-01-01 00:00:00) year to fraction(5)
nrows 2.000000000000
partn 10485965
partition sys_p5
exprtext
VALUES >= datetime(2018-01-01 00:00:00) year to fraction(5) AND VALUES < datetime(2019-01-01 00:00:00) year to fraction(5)
nrows 1.000000000000
partn 11534539
partition sys_p6
exprtext
VALUES >= datetime(2019-01-01 00:00:00) year to fraction(5) AND VALUES < datetime(2020-01-01 00:00:00) year to fraction(5)
nrows 2.000000000000
partn 12582914
partition sys_p7
exprtext
VALUES >= datetime(2020-01-01 00:00:00) year to fraction(5) AND VALUES < datetime(2021-01-01 00:00:00) year to fraction(5)
nrows 1.000000000000
partn 13631490
partition sys_p8
exprtext
VALUES >= datetime(2021-01-01 00:00:00) year to fraction(5) AND VALUES < datetime(2022-01-01 00:00:00) year to fraction(5)
nrows 2.000000000000
partn 14680267
partition sys_p9
exprtext
VALUES >= datetime(2022-01-01 00:00:00) year to fraction(5) AND VALUES < datetime(2023-01-01 00:00:00) year to fraction(5)
nrows 1.000000000000
12 row(s) retrieved.
