レンジパーティションのメンテナンス

• MySQL

(8.0.22)

drop table tab1;

create table tab1
( col1 int not null
, col2 timestamp not null
)
partition by range ( unix_timestamp(col2) )
( partition p1 values less than (unix_timestamp('2017-01-01 00:00:00') )
, partition p2 values less than (unix_timestamp('2018-01-01 00:00:00') )
, partition p3 values less than (unix_timestamp('2019-01-01 00:00:00') )
, partition p4 values less than (unix_timestamp('2020-01-01 00:00:00') )
);

alter table tab1 add constraint tab1pk primary key(col1,col2);

insert into tab1 values(10,'2016-04-01');
insert into tab1 values(20,'2017-04-01');
insert into tab1 values(30,'2018-04-01');
insert into tab1 values(40,'2019-04-01');

select * from tab1;
select * from tab1 partition(p1);
select * from tab1 partition(p2);
select * from tab1 partition(p3);
select * from tab1 partition(p4);

select table_name, partition_name, partition_expression, partition_description
from information_schema.partitions
where table_schema = 'test'
and table_name = 'tab1'
;

-- ①交換

drop table tab2;
create table tab2
( col1 int not null
, col2 timestamp not null
);
alter table tab2 add constraint tab2pk primary key(col1,col2);

insert into tab2 values(21,'2017-05-01');
select * from tab2;

alter table tab1 exchange partition p2 with table tab2;

-- ②分割

alter table tab1
reorganize partition p2 into (
partition p21 values less than (unix_timestamp('2017-07-01 00:00:00') )
,partition p22 values less than (unix_timestamp('2018-01-01 00:00:00') )
);

select * from tab1 partition(p21);
select * from tab1 partition(p22);

-- ③マージ

alter table tab1
reorganize partition p21,p22 into (
partition p2 values less than (unix_timestamp('2018-01-01 00:00:00') )
);

-- ④追加
alter table tab1 add partition (partition p5 values less than (unix_timestamp('2021-01-01 00:00:00') ) );

-- ⑤削除
alter table tab1 drop partition p5;

• Oracle

(19c)

drop table tab1 purge;

create table tab1
( col1 int not null primary key
, col2 date not null
)
partition by range (col2)
( partition p1 values less than (to_date('20170101','yyyymmdd'))
, partition p2 values less than (to_date('20180101','yyyymmdd'))
, partition p3 values less than (to_date('20190101','yyyymmdd'))
, partition p4 values less than (to_date('20200101','yyyymmdd'))
);

insert into tab1 values(10,to_date('20160401','yyyymmdd') );
insert into tab1 values(20,to_date('20170401','yyyymmdd') );
insert into tab1 values(30,to_date('20180401','yyyymmdd') );
insert into tab1 values(40,to_date('20190401','yyyymmdd') );
commit;

select * from tab1;
select * from tab1 partition(p1);
select * from tab1 partition(p2);
select * from tab1 partition(p3);
select * from tab1 partition(p4);

select table_name, partition_name, high_value
from dba_tab_partitions
where table_owner = 'TEST'
and table_name = 'TAB1'
;

-- ①交換
drop table tab2 purge;
create table tab2
( col1 int not null primary key
, col2 date not null
);

insert into tab2 values(21,to_date('20170501','yyyymmdd') );
commit;
select * from tab2;

alter table tab1 exchange partition p2 with table tab2 update indexes;

-- ②分割

alter table tab1
split partition p2 at (to_date('20170701','yyyymmdd'))
into ( partition p21, partition p22)
update indexes;

select * from tab1 partition(p21);
select * from tab1 partition(p22);

-- ③マージ
alter table tab1
merge partitions p21, p22
into partition p2
update indexes;

-- ④追加
alter table tab1 add partition p5 values less than (to_date('20210101','yyyymmdd')) update indexes;

-- ⑤削除
alter table tab1 drop partition p5 update indexes;

• PostgreSQL

(13)

drop table tab1 cascade;

create table tab1
( col1 int not null
, col2 timestamp not null
)
partition by range ( col2 )
;

alter table tab1 add constraint tab1pk primary key(col1,col2);

create table tab1p1 partition of tab1 for values from (minvalue) to ('2017-01-01');
create table tab1p2 partition of tab1 for values from ('2017-01-01') to ('2018-01-01');
create table tab1p3 partition of tab1 for values from ('2018-01-01') to ('2019-01-01');
create table tab1p4 partition of tab1 for values from ('2019-01-01') to ('2020-01-01');

insert into tab1 values(10,'2016-04-01');
insert into tab1 values(20,'2017-04-01');
insert into tab1 values(30,'2018-04-01');
insert into tab1 values(40,'2019-04-01');

select * from tab1;
select * from tab1p1;
select * from tab1p2;
select * from tab1p3;
select * from tab1p4;

select * from pg_partitioned_table;

select t1.inhparent, t2.relname, t1.inhrelid, t3.relname
from pg_inherits t1
inner join pg_class t2
on t1.inhparent = t2.oid
inner join pg_class t3
on t1.inhrelid = t3.oid
where t1.inhparent = 'tab1'::regclass::oid
;

-- ①交換
構文はない模様
-- ②分割
構文はない模様
-- ③マージ
構文はない模様

-- ④追加
drop table tab1p5;

create table tab1p5
( col1 int not null
, col2 timestamp not null
)
;

alter table tab1 attach partition tab1p5 for values from ('2020-01-01') to ('2021-01-01');

-- ⑤削除
alter table tab1 detach partition tab1p5;

• SQL Server

(2019)

alter database test add filegroup fg1;

alter database test
add file
(
name = fg1,
filename = 'c:\fg\fg1.ndf',
size = 5mb
)
to filegroup fg1;

create partition function pf1(datetime2)
as range right for
values ('2017/01/01', '2018/01/01', '2019/01/01', '2020/01/01')
;

create partition scheme ps1
as partition pf1
all to (fg1)
;

drop table tab1;
create table tab1
( col1 int not null
, col2 datetime2 not null
) on ps1(col2)
;

alter table tab1 add constraint tab1pk primary key(col1,col2);

insert into tab1 values(10,'2016-04-01');
insert into tab1 values(20,'2017-04-01');
insert into tab1 values(30,'2018-04-01');
insert into tab1 values(40,'2019-04-01');

select * from tab1;

select $partition.pf1(col2) as [partition_no], * from tab1;

select t2.name,t1.boundary_id,t1.value
from sys.partition_range_values t1
inner join sys.partition_functions t2
on t1.function_id = t2.function_id
where t2.name = 'pf1'
;

-- ①交換
drop table tab2;
create table tab2
( col1 int not null
, col2 datetime2 not null
) on fg1
;

※切替元と切替先は同じファイルグループの必要がある

alter table tab2 add constraint tab2pk primary key(col1,col2);

insert into tab2 values(21,'2017-05-01');
select * from tab2;

alter table tab2 switch to tab1 partition 2;

※ALTER TABLE SWITCH ステートメントが失敗しました。切り替え先テーブル 'test.dbo.tab1' の指定されたパーティション 2 を空にしてください。
→切替先は空にする必要がある
delete from tab1 where $partition.pf1(col2) = 2;

※ALTER TABLE SWITCH ステートメントが失敗しました。切り替え元テーブル 'test.dbo.tab2' のチェック制約では、切り替え先テーブル 'test.dbo.tab1' のパーティション 2 で定義された範囲では許可されない値が許可されます。
→切替元にチェック制約必要
alter table tab2 add constraint tab2chk check( col2 >= '2017/01/01' and col2 < '2018/01/01' );

-- ②分割

alter partition scheme ps1 next used fg1;
alter partition function pf1() split range('2017-07-01');

-- ③マージ

alter partition function pf1() merge range('2017-07-01');

-- ④追加
構文はない模様
-- ⑤削除
構文はない模様