(8.0.28)
drop table tab1;
create table tab1
( col1 int not null
, col2 varchar(10) not null
)
partition by list columns ( col2 )
( partition p1 values in ('AX','AY')
, partition p2 values in ('BX','BY')
, partition p3 values in ('CX','CY')
, partition p4 values in ('DX','DY')
);
alter table tab1 add constraint tab1pk primary key(col1,col2);
insert into tab1 values(10,'AX');
insert into tab1 values(20,'BX');
insert into tab1 values(30,'CX');
insert into tab1 values(40,'DX');
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 varchar(10) not null
);
alter table tab2 add constraint tab2pk primary key(col1,col2);
insert into tab2 values(21,'BY');
select * from tab2;
alter table tab1 exchange partition p2 with table tab2;
-- ②分割
alter table tab1
reorganize partition p2 into (
partition p21 values in ('BX')
,partition p22 values in ('BY')
);
select * from tab1 partition(p21);
select * from tab1 partition(p22);
-- ③マージ
alter table tab1
reorganize partition p21,p22 into (
partition p2 values in ('BX','BY')
);
-- ④追加
alter table tab1 add partition (partition p5 values in ('EX','EY') );
insert into tab1 values(50,'EX');
select * from tab1 partition(p5);
-- ⑤削除
alter table tab1 drop partition p5;
(19c)
drop table tab1 purge;
create table tab1
( col1 int not null primary key
, col2 varchar2(10) not null
)
partition by list (col2)
( partition p1 values ('AX','AY')
, partition p2 values ('BX','BY')
, partition p3 values ('CX','CY')
, partition p4 values ('DX','DY')
);
insert into tab1 values(10,'AX');
insert into tab1 values(20,'BX');
insert into tab1 values(30,'CX');
insert into tab1 values(40,'DX');
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 varchar2(10) not null
);
insert into tab2 values(21,'BY');
commit;
select * from tab2;
alter table tab1 exchange partition p2 with table tab2 update indexes;
-- ②分割
alter table tab1
split partition p2 values ('BX')
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('EX','EY') update indexes;
insert into tab1 values(50,'EX');
select * from tab1 partition(p5);
-- ⑤削除
alter table tab1 drop partition p5 update indexes;
(14)
create table tab1
( col1 int not null
, col2 varchar(10) not null
)
partition by list ( col2 )
;
alter table tab1 add constraint tab1pk primary key(col1,col2);
create table tab1p1 partition of tab1 for values in ('AX','AY');
create table tab1p2 partition of tab1 for values in ('BX','BY');
create table tab1p3 partition of tab1 for values in ('CX','CY');
create table tab1p4 partition of tab1 for values in ('DX','DY');
insert into tab1 values(10,'AX');
insert into tab1 values(20,'BX');
insert into tab1 values(30,'CX');
insert into tab1 values(40,'DX');
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 varchar(10) not null
)
;
alter table tab1 attach partition tab1p5 for values in ('EX','EY');
insert into tab1 values(50,'EX');
select * from tab1p5;
select * from tab1;
-- ⑤削除
alter table tab1 detach partition tab1p5;
select * from tab1p5;
select * from tab1;
(2019)
リストパーティション未対応
