パーティションテーブルへの変更

(8.0.22)

https://dev.mysql.com/doc/refman/8.0/ja/alter-table-partition-operations.html

 

drop table tab1;

create table tab1
( col1 int
, col2 int
, col3 int
)
;

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

create index ind12 on tab1(col2);
create index ind13 on tab1(col1,col2);
create index ind14 on tab1(col3,col1);


insert into tab1 values(100,100,100);
insert into tab1 values(200,200,200);
insert into tab1 values(300,300,300);
insert into tab1 values(400,400,400);

 

alter table tab1
partition by range (col1)
( partition p1 values less than (100)
, partition p2 values less than (200)
, partition p3 values less than (300)
, partition pmax values less than (maxvalue)
);

alter table tab1
partition by list (col1)
( partition p1 values in (100)
, partition p2 values in (200)
, partition p3 values in (300)
, partition pmax values in (400)
);

alter table tab1
partition by hash (col1)
( partition p1
, partition p2
, partition p3
, partition pmax
);

select count(*) from tab1 partition (p1);
select count(*) from tab1 partition (p2);
select count(*) from tab1 partition (p3);
select count(*) from tab1 partition (pmax);

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

show create table tab1;


※非パーティションテーブルに戻す場合

alter table tab1 remove partitioning;

パーティション方法の変更も可能

 

(19c)

https://qiita.com/mkyz08/items/a2346c55211268a8707d
https://docs.oracle.com/cd/F19136_01/vldbg/evolve-nopartition-table.html#GUID-6054142E-207A-4DF0-A62A-4C1A94DD36C4


12cR2から可能

drop table tab1 purge;

create table tab1
( col1 int
, col2 int
, col3 int
)
;
create index ind11 on tab1(col1);
alter table tab1 add constraint tab1pk primary key(col1) using index ind11;

create index ind12 on tab1(col2);
create index ind13 on tab1(col1,col2);
create index ind14 on tab1(col3,col1);

declare
begin
for i in 1..1000000 loop
insert into tab1 values(i,i,i);
commit;
end loop;
end;
/

 

alter table tab1 modify
partition by range (col1)
( partition p1 values less than (100)
, partition p2 values less than (200)
, partition p3 values less than (300)
, partition pmax values less than (maxvalue)
) ONLINE;

alter table tab1 modify
partition by list (col1)
( partition p1 values (100)
, partition p2 values (200)
, partition p3 values (300)
, partition pmax values (default)
) ONLINE;

alter table tab1 modify
partition by hash (col1)
( partition p1
, partition p2
, partition p3
, partition pmax
) ONLINE;


select count(*) from tab1 partition (p1);
select count(*) from tab1 partition (p2);
select count(*) from tab1 partition (p3);
select count(*) from tab1 partition (pmax);


select table_name,partition_name,high_value
from user_tab_partitions
where table_name = 'TAB1'
;

select table_name,partitioning_type
from user_part_tables
where table_name = 'TAB1'
;

select table_name,index_name,partitioned
from user_indexes
where table_name = 'TAB1'
;

→インデックスカラムにパーティションキーを含まない場合、グローバル非パーティションインデックスとなる

select index_name,partition_name,high_value
from user_ind_partitions
where index_name like 'IND1%'
;

select index_name,partitioning_type
from user_part_indexes
where table_name = 'TAB1'
;


※非パーティションテーブルに戻すことはできない模様
※19cの場合、パーティション方法の変更も可能

 

未対応の模様

(2019)
-- 1. 非パーティションテーブル用ファイルグループ作成
use master
go

alter database test remove file f11;
alter database test remove filegroup fg1;

alter database test add filegroup fg1;

alter database test add file
(name = N'f11',
 filename = N'c:\data\f11.ndf',
 size = 8mb,
 filegrowth = 0
) to filegroup fg1
;


-- 2. パーティション関数とパーティションスキーマの作成

use test
go

drop partition scheme ps1;
drop partition function pf1;

create partition function pf1(int)
as range right for
values (0)
;

create partition scheme ps1
as partition pf1
all to ([primary])
;

-- 3. テストテーブル作成。主キーがクラスタ化インデックスの場合と非クラスタ化インデックスの場合で確認する


drop table tab1;

create table tab1
  ( col1  int not null
  , col2  int
  , col3  int
  ) on fg1
;

-- alter table tab1 add constraint tab1pk primary key nonclustered(col1) on fg1;
alter table tab1 add constraint tab1pk primary key clustered(col1) on fg1;


insert into tab1 values(1,2,3);
insert into tab1 values(-1,-2,-3);
select * from tab1;


-- 4. パーティションテーブルへの変更

alter table tab1 drop constraint tab1pk;

alter table tab1 add constraint tab1pk primary key clustered(col1) on ps1(col1);


-- 5. 確認
select *, $partition.pf1(col1) as [パーティション番号] from tab1
;


select * from sys.dm_db_partition_stats where object_id = object_id('dbo.tab1')
;

select * from sys.database_files;

select top 100 t1.col1,t1.col2,t2.file_id,t2.page_id,t2.slot_id
from tab1 t1
cross apply sys.fn_PhysLocCracker(%%physloc%%) as t2
order by t2.file_id, t2.page_id, t2.slot_id
;


既存データも新しいファイルグループへ移動している模様