磐维2.0集中式分区转换

磐维数据库，简称"PanWeiDB"。是中国移动信息技术中心首个基于中国本土开源数据库打造的面向ICT基础设施的自研数据库产品。其产品内核能力基于openGauss开源软件，并进一步提升了系统稳定性及多种数据库兼容性。

分区转换

（1）创建分区表

create table t1 
( 
sid int not null, 
sname  varchar2(50) 
) 
PARTITION BY list(sname) 
( PARTITION p1 VALUES('test1'), 
  PARTITION p2 VALUES('test2'), 
  PARTITION p3 VALUES('test3') 
); 

（2）创建普通表，并创建索引

create table t11 
( 
sid int not null , 
sname  varchar2(50) 
);
create table t12 
( 
sid int not null , 
sname  varchar2(50) 
); 
create table t13 
( 
sid int not null , 
sname  varchar2(50) 
); 
create index ix_t11_sname on t11(sname);
create index ix_t12_sname on t12(sname);
create index ix_t13_sname on t13(sname);

（3）通过匿名块在普通表中插入数据

declare 
  maxrecords constant int:=4999; 
  i int :=1; 
begin 
  for i in 1..maxrecords loop 
    insert into t11 values(i,'test1'); 
  end loop; 
commit; 
end;
/
 
declare 
  maxrecords constant int:=9999; 
  i int :=5000; 
begin 
  for i in 5000..maxrecords loop 
    insert into t12 values(i,'test2'); 
  end loop; 
commit; 
end;
/

declare 
  maxrecords constant int:=70000; 
  i int :=10000; 
begin 
  for i in 10000..maxrecords loop 
    insert into t13 values(i,'test3'); 
  end loop; 
commit; 
end;
/

(4)进行分区表和普通表相互转换

# 普通表转换为分区表
alter table t1 exchange partition p1 with table t11;
alter table t1 exchange partition p2 with table t12;
alter table t1 exchange partition p3 with table t13;

## 分区表转换为普通表
# truncate原来的普通表
truncate table t11;
truncate table t12;
truncate table t13;

# 分区表转换为普通表
alter table t1 exchange partition p1 with table t11; 
alter table t1 exchange partition p2 with table t12; 
alter table t1 exchange partition p3 with table t13;

注意事项：使用分区转换时要注意索引，普通表要和分区表索引一致，否则会抛出异常ERROR: tables in ALTER TABLE EXCHANGE PARTITION must have the same number of indexs