ハッシュパーティションのメンテナンス

MySQL

(8.0.26)

drop table tab1;

create table tab1
( col1 bigint not null
, col2 bigint not null
)
partition by hash ( col2 )
( partition p1
, partition p2
, partition p3
, partition p4
, partition p5
, partition p6
, partition p7
, partition p8
);

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

insert into tab1 values(10,1 );
insert into tab1 values(20,2 );
insert into tab1 values(30,3 );
insert into tab1 values(40,4 );
insert into tab1 values(50,5 );
insert into tab1 values(60,6 );
insert into tab1 values(70,7 );
insert into tab1 values(80,8 );

select * from tab1;
select * from tab1 partition(p1);
select * from tab1 partition(p2);
select * from tab1 partition(p3);
select * from tab1 partition(p4);
select * from tab1 partition(p5);
select * from tab1 partition(p6);
select * from tab1 partition(p7);
select * from tab1 partition(p8);
select * from tab1 partition(p9);
select * from tab1 partition(p10);
select * from tab1 partition(p11);

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

select *
from information_schema.partitions
where table_schema = 'test'
and table_name = 'tab1'
;

-- ①交換

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

insert into tab2 values(90,9 );
select * from tab2;

alter table tab1 exchange partition p2 with table tab2;

-- ②追加

alter table tab1 add partition (partition p9) ;
alter table tab1 add partition (partition p10) ;
alter table tab1 add partition (partition p11) ;

-- ③結合

alter table tab1 coalesce partition 2;

※結合するパーティション数を指定する

Oracle

(19c)

drop table tab1 purge;

create table tab1
( col1 int not null primary key
, col2 int not null
)
partition by hash (col2)
( partition p1
, partition p2
, partition p3
, partition p4
, partition p5
, partition p6
, partition p7
, partition p8
);

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 int not null
);

insert into tab2 values(90,9 );
commit;
select * from tab2;

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

-- ②追加

alter table tab1 add partition p9 update indexes;
alter table tab1 add partition p10 update indexes;
alter table tab1 add partition p11 update indexes;

-- ③結合

alter table tab1 coalesce partition update indexes;

PostgreSQL

(14)

drop table tab1 cascade;

create table tab1
( col1 bigint not null
, col2 bigint not null
)
partition by hash ( col2 )
;

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

create table tab1p1 partition of tab1 for values with (modulus 8,remainder 0);
create table tab1p2 partition of tab1 for values with (modulus 8,remainder 1);
create table tab1p3 partition of tab1 for values with (modulus 8,remainder 2);
create table tab1p4 partition of tab1 for values with (modulus 8,remainder 3);
create table tab1p5 partition of tab1 for values with (modulus 8,remainder 4);
create table tab1p6 partition of tab1 for values with (modulus 8,remainder 5);
create table tab1p7 partition of tab1 for values with (modulus 8,remainder 6);
create table tab1p8 partition of tab1 for values with (modulus 8,remainder 7);

select * from tab1;
select * from tab1p1;
select * from tab1p2;
select * from tab1p3;
select * from tab1p4;
select * from tab1p5;
select * from tab1p6;
select * from tab1p7;
select * from tab1p8;

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
;

-- ①交換
構文はない模様

-- ②追加
構文はない模様

-- ③結合
構文はない模様

SQL Server

(2019)

ハッシュパーティション未対応