DM巧用分区交换将非分区表转换为分区表

本文链接：https://blog.csdn.net/robo23/article/details/122835189

在实际业务中，随着时间推移，表的数据量越来越大，影响了业务的读写性能；而如果表在设计之初使用的是普通表，没有使用分区表，此时随着系统的减慢，则考虑将大表转化为分区表。

非区分表转换为分区表的方法有多种，可以使用逻辑导出导入的方式转化，也可以借用insert into ... select的方式，本章介绍使用分区交换将非分区表转换为分区表。

构建测试表和测试数据

新建TEST用户，并赋予TEST用户创建表的权限；

使用TEST用户创建非分区表T_NOPARTITIONTAB，表结构参考如下：

create table t_nopartitiontab (  id      int,   name   varchar(20)) tablespace main;

在T_NOPARTITIONTAB表中插入1000行测试数据：

begin  for i in 1..1000 loop    insert into t_nopartitiontab(id, name)     values(i, DBMS_RANDOM.RANDOM_STRING('U',5)||':'|| lpad(i,4, '0'));  end loop;end;

创建分区表

创建分区表t_partitiontab，表字段结构与t_nopartitiontab相同，使用id字段范围分区。

create table t_partitiontab (  id      int,  name   varchar(20)) partition by range(id) (   partition p1 values less than (200),  partition p2 values less than (400),  partition p3 values less than (600),  partition p4 values less than (800),  partition pN values less than (maxvalue)

使用exchange交换分区

将非分区表t_nopartitiontab的数据交换到t_partitiontab表的pN分区。

alter table t_partitiontab exchange partition pN with table t_nopartitiontab;

分区交换后，t_nopartitiontab中数据为空，t_partitiontab中有1000行数据。

使用如下语句查询t_partitiontab分区表的数据分布：

select 'p1' partition_name, count(*) num from t_partitiontab partition(p1) union allselect 'p2', count(*) from t_partitiontab partition(p2) union allselect 'p3', count(*) from  t_partitiontab partition(p3) union allselect 'p4', count(*) from  t_partitiontab partition(p4) union allselect 'pN', count(*) from  t_partitiontab partition(pN);

可以看出普通非分区表的数据交换到分区表的某个分区后，数据并不进行校验，也就是说不管是否满足该分区的范围，数据都会进入该分区。

使用split将分区拆分

上述数据交换后，所有数据都在一个分区，完全不符合我们将数据放入分区表的初衷，此时可以使用分区拆分，分区拆分会对分区中的数据进行重组，拆分后数据会重新按照分区范围分布。执行以下命令进行分区拆分（仅范围分区支持分区拆分）：

alter table t_partitiontab split partition pN at (1000) into (partition p5, partition pmax);

分区拆分后，使用如下语句查询t_partitiontab分区表的数据分布：

select 'p1' partition_name, count(*) num from t_partitiontab partition(p1) union allselect 'p2', count(*) from t_partitiontab partition(p2) union allselect 'p3', count(*) from  t_partitiontab partition(p3) union allselect 'p4', count(*) from  t_partitiontab partition(p4) union allselect 'p5', count(*) from  t_partitiontab partition(p5) union allselect 'pmax', count(*) from  t_partitiontab partition(pmax);

查询结果如下，可以看出数据已经分布到各个范围的分区中：

merge合并分区

上述测试中，如果觉得pmax分区单独存在浪费，数据可以与分区p5合并，可以使用分区合并merge将p5和pmax合并，合并后的分区可以使用原来的名称，也可以使用新的名称，参考如下语句：

alter table t_partitiontab merge partitions p5, pmax into partition pN;

合并后，分区数据结果如下，可以看出数据已经合并在PN分区中：

使用分区交换exchange需注意：

1) 分区交换仅支持范围和列表分区，不支持HASH分区。

2) 分区交换可以将非分区表的数据交换到分区表，也可以将分区表的数据交换到非分区表，本质上是源表和目标表数据的互换。

3) 分区交换要求分区表与交换表具有相同的结构（相同的表类型、相同的BRANCH选项、相同的列结构、相同的索引、相同的分布方式），否则会报“[-7000]:交换对象不匹配”的错误。

4) 不支持含全局索引的分区表与普通表进行交换分区操作。

5) 分区交换时不会进行数据校验，如果交换表的数据不符合分区范围，数据仍然会进入该分区。此时如果是范围分区可以使用split拆分分区，系统会自动对数据进行重组。