パーティションメンテプロシージャ

引数1＝処理年月 例 202202

内部パラメータ：
未来件数 = 何か月先まで作成するか？ デフォルト  2
過去件数 = 何か月前まで保持するか？ デフォルト  2

仕様：
格納対象年月 をパーティション名とし、パーティション作成、削除の判定は格納対象年月を使用する
古いパーティションデータは別テーブルに退避後削除する

• MySQL

(8.0.31)

-- テストテーブル作成
drop table tab1;
drop table tab2;

create table tab1
( col1   bigint not null
, col2   datetime not null
)
 partition by RANGE COLUMNS (col2)
 ( partition p202201 values less than ('2022-02-01')
 , partition p202202 values less than ('2022-03-01')
 , partition p202203 values less than ('2022-04-01')
 , partition p202204 values less than ('2022-05-01')
 , partition pmax values less than (MAXVALUE)
 );

create table tab2
( col1   bigint not null
, col2   datetime not null
)
 partition by RANGE COLUMNS (col2)
 ( partition p202201 values less than ('2022-02-01')
 , partition p202202 values less than ('2022-03-01')
 , partition p202203 values less than ('2022-04-01')
 , partition p202204 values less than ('2022-05-01')
 , partition pmax values less than (MAXVALUE)
 );

insert into tab1 values(10,'2022-01-20' );
insert into tab2 values(10,'2032-01-20' );

select table_name,partition_name,partition_description
from information_schema.partitions
where table_schema = 'test'
and table_name in ('tab1','tab2')
order by table_name,partition_name
;

select count(*) from tab1;
select count(*) from tab2;

select count(*) from tab1_taihi;
select count(*) from tab2_taihi;

-- メンテプロシージャ作成

drop procedure proc_partition_mainte;
delimiter //
create procedure proc_partition_mainte(in p_yyyymm varchar(6) )
begin

declare va1 varchar(4000);
declare va2 varchar(4000);
declare va3 varchar(4000);
declare va4 varchar(4000);
declare va5 varchar(4000);

declare wk1 numeric;

declare C_FUTURE_COUNT numeric default 2;
declare C_PAST_COUNT numeric default 2;

declare done integer;

declare cur1 cursor for select distinct table_name
                        from information_schema.partitions
                        where table_schema = 'test'
                        and REGEXP_LIKE(partition_name, 'p:digit:{6}')
                        order by table_name
                        ;

declare continue handler for sqlstate '02000' set done = 1;
declare continue handler for sqlstate 'HY000' set done = 0;

-- 未来年月取得(パーティション名用)

set @q := concat('select DATE_ADD( concat(substr(''',p_yyyymm,''',1,4),''-'',substr(''',p_yyyymm,''',5,2),''-01''), INTERVAL ',C_FUTURE_COUNT,' MONTH) into @val');
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set va1 = ( select replace(substr(@val,1,7),'-','') );

-- 未来年月日取得(パーティション境界用)

set @q := concat('select DATE_ADD( concat(substr(''',p_yyyymm,''',1,4),''-'',substr(''',p_yyyymm,''',5,2),''-01''), INTERVAL ',C_FUTURE_COUNT+1,' MONTH) into @val');
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set va2 = @val;

-- 過去年月取得(パーティション名用)
set @q := concat('select DATE_ADD( concat(substr(''',p_yyyymm,''',1,4),''-'',substr(''',p_yyyymm,''',5,2),''-01''), INTERVAL ',-1*C_PAST_COUNT,' MONTH) into @val');
prepare stmt from @q;
execute stmt;
deallocate prepare stmt;
set va3 = ( select replace(substr(@val,1,7),'-','') );

set done = 0;
open cur1;
fetch cur1 into va4;
while done != 1 do

  -- 退避テーブル名
  set va5 = concat(va4,'_taihi');

  -- 退避テーブル存在チェック
  set wk1 = ( select count(*) from  information_schema.tables where table_schema='test' and table_name  = va5 );

  -- 退避テーブル作成
  if wk1 = 0 then
    set @q = concat(' create table ',va5,' as select * from ',va4,' where 1=2 ' );
    prepare stmt from @q;
    execute stmt;
    deallocate prepare stmt;
  end if;

  -- メンテ実行

  set @q = concat('alter table ',va4);
  set @q = concat(@q ,' reorganize partition pmax into ( ');
  set @q = concat(@q ,' partition p',va1,' values less than (''',va2,''') ');
  set @q = concat(@q ,' ,partition pmax values less than ( MAXVALUE ) )');
  prepare stmt from @q;
  execute stmt;
  deallocate prepare stmt;

  set @q = concat(' insert into ',va5,' select * from ',va4,' partition(p',va3,') ');
  prepare stmt from @q;
  execute stmt;
  deallocate prepare stmt;

  set @q = concat(' alter table ',va4,' drop partition p',va3,' ');
  prepare stmt from @q;
  execute stmt;
  deallocate prepare stmt;

  fetch cur1 into va4;
end while;
close cur1;

end
//
delimiter ;

-- 動作確認

call proc_partition_mainte('202203');
call proc_partition_mainte('202204');
call proc_partition_mainte('202205');

select table_name,partition_name,partition_description
from information_schema.partitions
where table_schema = 'test'
and table_name in ('tab1','tab2')
order by table_name,partition_name
;

select count(*) from tab1;
select count(*) from tab2;

select count(*) from tab1_taihi;
select count(*) from tab2_taihi;

• Oracle

(19c)

-- テストテーブル作成
drop table tab1 purge;
drop table tab2 purge;

create table tab1
( col1   int not null
, col2   timestamp not null
)
 partition by range (col2)
 ( partition p202201 values less than (to_date('20220201','yyyymmdd') )
 , partition p202202 values less than (to_date('20220301','yyyymmdd') )
 , partition p202203 values less than (to_date('20220401','yyyymmdd') )
 , partition p202204 values less than (to_date('20220501','yyyymmdd') )
 , partition pmax values less than (MAXVALUE)
 );

create table tab2
( col1   int not null
, col2   timestamp not null
)
 partition by range (col2)
 ( partition p202201 values less than (to_date('20220201','yyyymmdd') )
 , partition p202202 values less than (to_date('20220301','yyyymmdd') )
 , partition p202203 values less than (to_date('20220401','yyyymmdd') )
 , partition p202204 values less than (to_date('20220501','yyyymmdd') )
 , partition pmax values less than (MAXVALUE)
 );

insert into tab1 values(10,to_date('20220120','yyyymmdd') );
insert into tab2 values(10,to_date('20320120','yyyymmdd') );
commit;

select table_name,partition_name,high_value
from user_tab_partitions
where table_name in ('TAB1','TAB2')
order by table_name,partition_name
;

select count(*) from tab1;
select count(*) from tab2;

select count(*) from tab1_taihi;
select count(*) from tab2_taihi;

-- メンテプロシージャ作成

set serveroutput on
create or replace procedure proc_partition_mainte(p_yyyymm in varchar2)
authid current_user
as

cursor cur1 is select distinct table_name 
               from user_tab_partitions 
               where REGEXP_LIKE(partition_name, 'P\d{6}')
               order by table_name;

va1 varchar2(4000);
va2 varchar2(4000);
va3 varchar2(4000);
va4 varchar2(4000);

wk1 number;

C_FUTURE_COUNT number := 2;
C_PAST_COUNT number := 2;

vsql varchar2(4000);

begin

-- 未来年月取得(パーティション名用)
select to_char(add_months(to_date(p_yyyymm||'01'),C_FUTURE_COUNT) ,'YYYYMM') into va1 from dual;

-- 未来年月日取得(パーティション境界用)
select to_char(add_months(to_date(p_yyyymm||'01'),C_FUTURE_COUNT+1) ,'YYYYMMDD') into va2 from dual;

-- 過去年月取得(パーティション名用)
select to_char(add_months(to_date(p_yyyymm||'01'),-1*C_PAST_COUNT) ,'YYYYMM') into va3 from dual;

for c1 in cur1 loop
  -- 退避テーブル名
  va4 := c1.table_name||'_TAIHI';

  -- 退避テーブル存在チェック
  select count(*) into wk1 from user_tables where table_name = va4;

  -- 退避テーブル作成
  if wk1 = 0 then
    vsql := 'create table '||va4||' as select * from '||c1.table_name||' where 1=2 ';
    execute immediate vsql;
  end if;

  -- メンテ実行
  vsql := '';
  vsql := vsql || ' alter table '|| c1.table_name;
  vsql := vsql || ' split partition pmax at (to_date('''||va2||''',''yyyymmdd'') ) ';
  vsql := vsql || ' into ( partition p'||va1||', partition pmax) ';
  vsql := vsql || ' update indexes ';

  begin
    execute immediate vsql;
  exception when others then
    null;
  end;

  vsql := ' insert into '||va4||' select * from '||c1.table_name||' partition(p'||va3||') ';
  begin
    execute immediate vsql;
  exception when others then
    null;
  end;

  vsql := ' alter table '||c1.table_name||' drop partition p'||va3||' update indexes ';
  begin
    execute immediate vsql;
  exception when others then
    null;
  end;

end loop;

end;
/
sho error

-- 動作確認

exec proc_partition_mainte('202203');
exec proc_partition_mainte('202204');
exec proc_partition_mainte('202205');

select table_name,partition_name,high_value
from user_tab_partitions
where table_name in ('TAB1','TAB2')
order by table_name,partition_name
;

select count(*) from tab1;
select count(*) from tab2;

select count(*) from tab1_taihi;
select count(*) from tab2_taihi;

• PostgreSQL

(15)

-- テストテーブル作成
drop table tab1;
drop table tab2;

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

create table tab1p202201 partition of tab1 for values from (MINVALUE) to ('2022-02-01');
create table tab1p202202 partition of tab1 for values from ('2022-02-01') to ('2022-03-01');
create table tab1p202203 partition of tab1 for values from ('2022-03-01') to ('2022-04-01');
create table tab1p202204 partition of tab1 for values from ('2022-04-01') to ('2022-05-01');
create table tab1pmax    partition of tab1 for values from ('2022-05-01') to (MAXVALUE);

create table tab2
( col1   bigint not null
, col2   timestamp not null
)
partition by range ( col2 )
;

create table tab2p202201 partition of tab2 for values from (MINVALUE) to ('2022-02-01');
create table tab2p202202 partition of tab2 for values from ('2022-02-01') to ('2022-03-01');
create table tab2p202203 partition of tab2 for values from ('2022-03-01') to ('2022-04-01');
create table tab2p202204 partition of tab2 for values from ('2022-04-01') to ('2022-05-01');
create table tab2pmax    partition of tab2 for values from ('2022-05-01') to (MAXVALUE);

insert into tab1 values(10,'2022-01-20' );
insert into tab2 values(10,'2032-01-20' );

\d+ tab1
\d+ tab2

select count(*) from tab1;
select count(*) from tab2;

select count(*) from tab1_taihi;
select count(*) from tab2_taihi;

-- メンテプロシージャ作成

drop procedure proc_partition_mainte;

create or replace procedure proc_partition_mainte(
 p_yyyymm        in varchar 
)
language plpgsql
as $$
declare

cur1 cursor for select distinct t1.tablename 
                from pg_tables t1
                where t1.schemaname = 'public'
                and exists ( select 1 from pg_class t2
                                      where t2.relkind = 'p'
                                      and t2.relnamespace = t1.schemaname::regnamespace
                                      and t2.relname = t1.tablename )
                and exists ( select REGEXP_MATCHES(t3.tablename , '^'||t1.tablename||'p[0-9]{6}') from pg_tables t3
                                      where t1.schemaname = t3.schemaname )
                order by t1.tablename;

va1 varchar(4000);
va2 varchar(4000);
va3 varchar(4000);
va4 varchar(4000);
va5 varchar(4000);

wk1 numeric;

C_FUTURE_COUNT numeric := 2;
C_PAST_COUNT numeric := 2;

vsql varchar(4000);

begin

-- 未来年月取得(パーティション名用)

vsql := 'select to_char( ('''||p_yyyymm||'''||''01'')::date  + interval '''||C_FUTURE_COUNT||' month'',''YYYYMM'') ';
execute vsql into va1;

-- 未来年月日取得(パーティション境界用)

vsql := 'select to_char( ('''||p_yyyymm||'''||''01'')::date  + interval '''||C_FUTURE_COUNT||' month'',''YYYY-MM-DD'') ';
execute vsql into va2;

vsql := 'select to_char( ('''||p_yyyymm||'''||''01'')::date  + interval '''||C_FUTURE_COUNT+1||' month'',''YYYY-MM-DD'') ';
execute vsql into va3;

-- 過去年月取得(パーティション名用)

vsql := 'select to_char( ('''||p_yyyymm||'''||''01'')::date  + interval '''|| (-1)*C_PAST_COUNT||' month'',''YYYYMM'') ';
execute vsql into va4;

for c1 in  cur1 loop
    
  -- 退避テーブル名
  va5 := c1.tablename||'_taihi';
    
  -- 退避テーブル存在チェック
  select count(*) strict into wk1 from pg_tables where schemaname = 'public' and tablename = va5;

  -- 退避テーブル作成
  if wk1 = 0 then
    vsql := ' create table '||va5||' ( like '|| c1.tablename ||' )';
    execute vsql ;
  end if;

  -- メンテ実行
  begin
    vsql := ' alter table '||c1.tablename||' detach partition '||c1.tablename||'pmax ';
    execute vsql;

    vsql := ' alter table '||c1.tablename||'pmax rename to '||c1.tablename||'pmax_old ';
    execute vsql;

    vsql := ' create table '||c1.tablename||'p'||va1||' partition of '||c1.tablename||' for values from ('''||va2||''') to ('''||va3||''') ';
    execute vsql;

    vsql := ' create table '||c1.tablename||'pmax    partition of '||c1.tablename||' for values from ('''||va3||''') to (MAXVALUE) ';
    execute vsql;

    vsql := ' insert into '||c1.tablename||' select * from '||c1.tablename||'pmax_old ';
    execute vsql;

    vsql := ' drop table '||c1.tablename||'pmax_old ';
    execute vsql;

    vsql := ' alter table '||c1.tablename||' detach partition '||c1.tablename||'p'||va4;
    execute vsql;

    vsql := ' insert into '||va5||' select * from '||c1.tablename||'p'||va4;
    execute vsql;

    vsql := ' drop table '||c1.tablename||'p'||va4;
    execute vsql;
  exception when others then
    null;
  end;
    
end loop;

end;
$$;

-- 動作確認

call proc_partition_mainte('202203');
call proc_partition_mainte('202204');
call proc_partition_mainte('202205');

\d+ tab1
\d+ tab2

select count(*) from tab1;
select count(*) from tab2;

select count(*) from tab1_taihi;
select count(*) from tab2_taihi;

• SQL Server

(2019)

-- テストテーブル作成
drop table tab1;
drop table tab2;

drop partition scheme ps1;
drop partition function pf1;

create partition function pf1(datetime2) as range right 
 for values ('2022-02-01','2022-03-01','2022-04-01','2022-05-01');
create partition scheme ps1 as partition pf1 all to ([PRIMARY]);

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

create table tab2
( col1   bigint not null
, col2   datetime2 not null
) on ps1(col2)
;

insert into tab1 values(10,'2022-01-20' );
insert into tab2 values(10,'2032-01-20' );

select distinct object_name(i.object_id)  table_name ,
  pf.name  partitionfunction, 
  ps.name  partitionscheme, 
  rv.value  range_value
from sys.indexes i
inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
inner join sys.partition_functions pf on pf.function_id = ps.function_id
inner join sys.partition_range_values rv on pf.function_id = rv.function_id 
where object_name(i.object_id) in ('tab1','tab2')
order by table_name, range_value;

select count(*) from tab1;
select count(*) from tab2;

select count(*) from tab1_taihi;
select count(*) from tab2_taihi;

-- メンテプロシージャ作成

create or alter procedure proc_partition_mainte(
 @p_yyyymm        varchar(4000) 
)
as
begin
set nocount on;

declare @va1 varchar(4000);
declare @va2 varchar(4000);
declare @va3 varchar(4000);
declare @va4 varchar(4000);
declare @va5 varchar(4000);

declare @wk1 numeric;

declare @C_FUTURE_COUNT numeric = 2;
declare @C_PAST_COUNT numeric = 2;

declare @vsql nvarchar(4000);

-- 未来年月日取得(パーティション境界用)

set @va1 = (select format(dateadd(month, @C_FUTURE_COUNT+1,cast(@p_yyyymm+'01' as datetime2) ),'yyyy-MM-dd') )

-- 過去年月日取得(パーティション境界用)

set @va2 = (select format(dateadd(month, (-1)*@C_PAST_COUNT+1,cast(@p_yyyymm+'01' as datetime2) ),'yyyy-MM-dd') )

declare cur1 cursor for select distinct object_name(i.object_id)  table_name
                        from sys.indexes i
                        inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
                        inner join sys.partition_functions pf on pf.function_id = ps.function_id
                        where pf.name = 'pf1'
                        order by table_name;

open cur1;
fetch next from cur1 into @va3;
while @@fetch_status = 0
begin

  -- 退避テーブル名
  set @va4 = concat(@va3,'_taihi');

  -- 退避テーブル存在チェック
  set @wk1 = ( select count(*) from sys.tables where name = @va4 );

  -- 退避テーブル作成
  if @wk1 = 0
    begin
      set @vsql = N' select * into '+@va4+' from '+@va3+' where 1=2 ';
      execute (@vsql);
    end;

-- パーティションキー取得

  set @vsql = N' select @va5 = c.name ';
  set @vsql = @vsql + N' from sys.tables  t ';
  set @vsql = @vsql + N' join sys.indexes  i ';
  set @vsql = @vsql + N'   on t.object_id = i.object_id ';
  set @vsql = @vsql + N'   and i.type <= 1 ';
  set @vsql = @vsql + N' join sys.partition_schemes  ps ';
  set @vsql = @vsql + N'   on ps.data_space_id = i.data_space_id ';
  set @vsql = @vsql + N' join sys.index_columns  ic ';
  set @vsql = @vsql + N'   on ic.object_id = i.object_id ';
  set @vsql = @vsql + N'   and ic.index_id = i.index_id ';
  set @vsql = @vsql + N'   and ic.partition_ordinal >= 1 ';
  set @vsql = @vsql + N' join sys.columns  c ';
  set @vsql = @vsql + N'   on ic.object_id = c.object_id ';
  set @vsql = @vsql + N'   and ic.column_id = c.column_id ';
  set @vsql = @vsql + N' where t.name = '''+@va3+''' ';

  execute sp_executesql
    @vsql, N'@va5 varchar(4000) OUT', @va5 OUT;

  -- メンテ実行(データ退避)
  begin try
    set @vsql = N' insert into '+@va4+' select * from '+@va3+' where '+@va5+' < '''+@va2+''' ';
    execute (@vsql);

    set @vsql = N' delete from '+@va3+' where '+@va5+' < '''+@va2+''' ';
    execute (@vsql);
  end try
  begin catch
  end catch

  fetch next from cur1 into @va3;
end
close cur1;
deallocate cur1;

-- メンテ実行(パーティション分割マージ)
begin try
  set @vsql = N' alter partition scheme ps1 next used [PRIMARY] ';
  execute (@vsql);

  set @vsql = N' alter partition function pf1() split range('''+@va1+''') ';
  execute (@vsql);

  set @vsql = N' alter partition function pf1() merge range('''+@va2+''') ';
  execute (@vsql);
end try
begin catch
end catch

end
go

-- 動作確認

exec proc_partition_mainte '202203';
exec proc_partition_mainte '202204';
exec proc_partition_mainte '202205';

select distinct object_name(i.object_id)  table_name ,
  pf.name  partitionfunction, 
  ps.name  partitionscheme, 
  rv.value  range_value
from sys.indexes i
inner join sys.partition_schemes ps on ps.data_space_id = i.data_space_id
inner join sys.partition_functions pf on pf.function_id = ps.function_id
inner join sys.partition_range_values rv on pf.function_id = rv.function_id 
where object_name(i.object_id) in ('tab1','tab2')
order by table_name, range_value;

select count(*) from tab1;
select count(*) from tab2;

select count(*) from tab1_taihi;
select count(*) from tab2_taihi;