表分区是数据库性能优化、数据运维的常见手段，需要在系统设计或表结构设计初期便要考虑好使用场景，评估性能校准及数据卸载方案，本文介绍DM达梦8分区表类型、使用及维护方法。
分区表类型：水平分区、垂直分区
水平分区：范围、哈希、列表三种方式。另外，多集分区表是将三种方式组合、嵌套使用，形成多集分区表
分区的好处：可以将大表或索引分割成独立的、可存储在不同存储介质上的小的单位，提升查询性能、均衡IO、高可用性，降低维护成本。
分区概念
将大表或索引等数据库对象，分割成较小的可管理片段，每个片段被称为分区（表或索引）。
分区方法
范围水平分区
Range：对表中某列上值的范围进行分区，根据某个值的范围决定将该数据存储在哪个分区上；
哈希水平分区
hash：通过指定分区编号来均匀分布数据，通过在IO设备上进行散列分区，使得这些分区大小基本一致；
列表水平分区
list：通过指定表中的某个列的离散值集，来确定应该存放在一起的数据；
多级分区
以上分区方式的混合分级使用；
创建分区表
创建范围分区表
总共1个语句正依次执行...
[执行语句1]:
create
TABLE T_R1
(
ID int,
NAME VARCHAR(20)
)
PARTITION BY RANGE
(
ID
)
(
PARTITION P1 VALUES LESS THAN(100),
PARTITION P2 VALUES LESS THAN(200)
);
警告:范围分区未包含MAXVALUE,可能无法定位到分区
执行成功, 执行耗时67毫秒. 执行号:1885
影响了0条记录
1条语句执行成功
select
DBA_TABLES.TABLE_NAME,
DBA_TABLES.PARTITIONED
from
SYS.DBA_TABLES
WHERE
DBA_TABLES.TABLE_NAME = 'T_R1';
查询表分区
select
DBA_TAB_PARTITIONS.TABLE_NAME,
DBA_TAB_PARTITIONS.PARTITION_NAME
from
SYS.DBA_TAB_PARTITIONS
where
DBA_TAB_PARTITIONS.TABLE_NAME = 'T_R1';
灌数据验证一下
begin
FOR I IN 1..150
LOOP
insert into SYSDBA.T_R1(T_R1.ID, T_R1.NAME) VALUES(I, 'AA' || I);
commit;
end LOOP;
end;
select count(0) from SYSDBA.T_R1 PARTITION(P1);
插入300，提示没有找到合适的分区
insert into SYSDBA.T_R1 VALUES(300,'AA300');
总共1个语句正依次执行...
[执行语句1]:
insert into SYSDBA.T_R1 VALUES(300,'AA300');
执行失败(语句1)
没有找到合适的分区
1条语句执行失败
查询分区最高值
select
DBA_TAB_PARTITIONS.PARTITION_NAME,
DBA_TAB_PARTITIONS.HIGH_VALUE
from
SYS.DBA_TAB_PARTITIONS
where
DBA_TAB_PARTITIONS.TABLE_NAME = 'T_R1';
新增分区
alter TABLE SYSDBA.T_R1 ADD PARTITION PN VALUES LESS THAN(MAXVALUE);
insert INTO SYSDBA.T_R1 VALUES(300,'AA300');
insert INTO SYSDBA.T_R1 VALUES(400,'AA400');
select * from SYSDBA.T_R1_PN;
不同分区放在不同表空间
总共1个语句正依次执行...
[执行语句1]:
create
TABLE T_R3
(
SID int,
ID int,
NAME VARCHAR(20)
)
PARTITION BY RANGE
(
SID
)
(
PARTITION P1 VALUES LESS THAN(100) TABLESPACE TBS1,
PARTITION P2 VALUES LESS THAN(200) TABLESPACE TBS2
);
警告:范围分区未包含MAXVALUE,可能无法定位到分区
执行成功, 执行耗时66毫秒. 执行号:2180
影响了0条记录
1条语句执行成功
创建水平分区堆表
总共1个语句正依次执行...
[执行语句1]:
create
TABLE T_R4
(
SID INT,
ID INT,
NAME VARCHAR(20)
)
PARTITION BY RANGE
(
SID
)
(
PARTITION P1 VALUES LESS THAN(100) TABLESPACE TBS1,
PARTITION P2 VALUES LESS THAN(200) TABLESPACE TBS2,
PARTITION P3 VALUES LESS THAN(MAXVALUE) TABLESPACE TBS2
)
STORAGE
(
NOBRANCH
);
执行失败(语句1)
第20 行附近出现错误[-2757]:
水平分区堆表各子表必须位于同一个表空间
1条语句执行失败
dm8目前不允许将堆表的分区放在不同的表空间里。
解决方法：放在同一个表空间中
总共1个语句正依次执行...
[执行语句1]:
create
TABLE T_R4
(
SID INT,
ID INT,
NAME VARCHAR(20)
)
PARTITION BY RANGE
(
SID
)
(
PARTITION P1 VALUES LESS THAN(100),
PARTITION P2 VALUES LESS THAN(200),
PARTITION P3 VALUES LESS THAN(MAXVALUE)
)
STORAGE
(
ON TBS1,
NOBRANCH
);
执行成功, 执行耗时34毫秒. 执行号:2258
影响了0条记录
1条语句执行成功
创建列表分区表
列表分区表分区列适合字符型
总共1个语句正依次执行...
[执行语句1]:
CREATE
TABLE T_R5
(
SID INT ,
NAME VARCHAR(20),
CITY VARCHAR(20)
)
PARTITION BY LIST
(
CITY
)
(
PARTITION P1 VALUES
(
'石家庄',
'天津'
)
,
PARTITION P2 VALUES
(
'上海',
'苏州',
'杭州'
)
,
PARTITION PN VALUES
(
DEFAULT
)
);
执行成功, 执行耗时44毫秒. 执行号:2286
影响了0条记录
1条语句执行成功
创建hash分区表
由于官方文档中提到哈希分区存数据快，但通过测试并没有发现哈希分区比普通表存数据快，相反还慢了不好，不知道这里官方文档所说的性能好，是与那种类型的表作对比。
总共1个语句正依次执行...
[执行语句1]:
CREATE TABLE T_R6(ID int) PARTITION BY HASH(ID) PARTITIONS 10;
执行成功, 执行耗时37毫秒. 执行号:2301
影响了0条记录
1条语句执行成功
最后一个数字10，代表分成10个hash分区，如果后面跟STORE IN (TBS1,TBS2,TBS3 ….)，则表示分区放在不同的表空间。
测试插入数据性能
总共1个语句正依次执行...
[执行语句1]:
BEGIN
FOR I IN 0..1000 LOOP
INSERT INTO SYSDBA.T_R6 VALUES(I);
COMMIT;
END LOOP;
END;
执行成功, 执行耗时258毫秒. 执行号:2340
影响了1条记录
1条语句执行成功
我高估了dm的性能，测试用例插入100w行，两分半还没执行完?
总共1个语句正依次执行...
[执行语句1]:
BEGIN
FOR I IN 0..1000000 LOOP
INSERT INTO SYSDBA.T_R6 VALUES(I);
COMMIT;
END LOOP;
END;
执行成功, 执行耗时3分 3秒 978毫秒. 执行号:2366
影响了1条记录
1条语句执行成功
最终3分多执行完成，并没有感觉很快，接下来测试普通表插入100w行记录
总共1个语句正依次执行...
[执行语句1]:
BEGIN
FOR I IN 0..1000000 LOOP
INSERT INTO SYSDBA.T_R7 VALUES(I);
COMMIT;
END LOOP;
END;
执行成功, 执行耗时2分 43秒 886毫秒. 执行号:2409
影响了1条记录
1条语句执行成功
实测普通表插入更快，原因应该很好理解，hash分区表在插入的时候要计算哈希值来确定放在哪个分区里。
创建多级分区表
eg.创建list-range分区表，销售记录表，按照区域和时间做多级分区划分
总共1个语句正依次执行...
[执行语句1]:
CREATE
TABLE SALES
(
SALES_ID INT ,
SALEMAN CHAR(20),
SALEDATE DATE ,
CITY CHAR(20)
)
PARTITION BY LIST
(
CITY
)
SUBPARTITION BY RANGE
(
SALEDATE
)
SUBPARTITION TEMPLATE --多级分区中，每个分区的二级分区模板，如果一级子分区没有自定义二级子分区，则按这个模板创建
(
SUBPARTITION P11 VALUES LESS THAN('2018-12-31'),
SUBPARTITION P12 VALUES LESS THAN('2019-12-31'),
SUBPARTITION P13 VALUES LESS THAN('2020-12-31'),
SUBPARTITION P14 VALUES LESS THAN(MAXVALUE)
)
(
PARTITION P1 VALUES
(
'石家庄',
'天津'
)
(SUBPARTITION P11_1 VALUES EQU
OR LESS THAN('2019-06-30'), SUBPARTITION P11_2 VALUES EQU
OR LESS THAN(MAXVALUE) ),
PARTITION P2 VALUES
(
'上海',
'杭州'
)
,
PARTITION P3 VALUES
(
DEFAULT
)
);
执行成功, 执行耗时36毫秒. 执行号:2445
影响了0条记录
1条语句执行成功
这样，就会按照区域和时间，将记录存储到对应的分区中，非常有利于大表数据拆分存储，提高查询性能。
可以看到P1子表被我们自定义了，P2按模板进行了子表划分；
数据进入到了对应的表分区中。
达梦最多支持8级分区表。
分区表限制
- 水平分区表（水平分区包括范围、哈希和列表三种方式）指定主键与唯一索引时，分区键必须都包含在主键和唯一约束中。
总共1个语句正依次执行...
[执行语句1]:
CREATE
TABLE T_R8
(
SID INT ,
ID INT ,
NAME VARCHAR(20),
CONSTRAINT T_R8_PRI PRIMARY KEY(SID)
)
PARTITION BY RANGE
(
ID
)
(
PARTITION P1 VALUES LESS THAN(100),
PARTITION P2 VALUES LESS THAN(200),
PARTITION P3 VALUES LESS THAN(MAXVALUE)
);
执行失败(语句1)
第17 行附近出现错误[-2683]:
局部唯一索引必须包含全部分区列
1条语句执行失败
总共1个语句正依次执行...
[执行语句1]:
CREATE
TABLE T_R8
(
SID INT ,
ID INT ,
NAME VARCHAR(20),
CONSTRAINT T_R8_PRI PRIMARY KEY(SID)
)
PARTITION BY RANGE
(
SID
)
(
PARTITION P1 VALUES LESS THAN(100),
PARTITION P2 VALUES LESS THAN(200),
PARTITION P3 VALUES LESS THAN(MAXVALUE)
);
执行成功, 执行耗时36毫秒. 执行号:2538
影响了0条记录
1条语句执行成功
增加一个唯一索引列
总共1个语句正依次执行...
[执行语句1]:
CREATE
TABLE T_R8
(
SID INT ,
ID INT ,
NAME VARCHAR(20) ,
CONSTRAINT T_R8_PRI PRIMARY KEY(SID),
CONSTRAINT T_R8_UNI UNIQUE(ID)
)
PARTITION BY RANGE
(
SID
)
(
PARTITION P1 VALUES LESS THAN(100),
PARTITION P2 VALUES LESS THAN(200),
PARTITION P3 VALUES LESS THAN(MAXVALUE)
);
执行失败(语句1)
第18 行附近出现错误[-2683]:
局部唯一索引必须包含全部分区列
1条语句执行失败
解决方法一：创建SID与ID的联合主键
总共1个语句正依次执行...
[执行语句1]:
CREATE
TABLE T_R8
(
SID INT ,
ID INT ,
NAME VARCHAR(20) ,
CONSTRAINT T_R8_PRI PRIMARY KEY(SID,ID)--,
--CONSTRAINT T_R8_UNI UNIQUE(ID)
)
PARTITION BY RANGE
(
SID,
ID
)
(
PARTITION P1 VALUES LESS THAN(100, 200),
PARTITION P2 VALUES LESS THAN(200, 300),
PARTITION P3 VALUES LESS THAN(MAXVALUE, MAXVALUE)
);
执行成功, 执行耗时49毫秒. 执行号:2549
影响了0条记录
1条语句执行成功
创建成功，但是感觉这个方案很猥琐！
方法二：改为堆表
总共1个语句正依次执行...
[执行语句1]:
CREATE
TABLE T_R8
(
SID INT ,
ID INT ,
NAME VARCHAR(20) ,
CONSTRAINT T_R8_PRI PRIMARY KEY(SID),
CONSTRAINT T_R8_UNI UNIQUE(ID)
)
PARTITION BY RANGE
(
SID,
ID
)
(
PARTITION P1 VALUES LESS THAN(100, 200),
PARTITION P2 VALUES LESS THAN(200, 300),
PARTITION P3 VALUES LESS THAN(MAXVALUE, MAXVALUE)
) STORAGE(NOBRANCH);
执行成功, 执行耗时7毫秒. 执行号:2551
影响了0条记录
1条语句执行成功
创建成功。
总共1个语句正依次执行...
[执行语句1]:
CREATE
TABLE T_R8
(
SID INT ,
ID INT ,
NAME VARCHAR(20) ,
CONSTRAINT T_R8_PRI PRIMARY KEY(SID),
CONSTRAINT T_R8_UNI UNIQUE(ID)
)
PARTITION BY RANGE
(
SID
)
(
PARTITION P1 VALUES LESS THAN(100),
PARTITION P2 VALUES LESS THAN(200),
PARTITION P3 VALUES LESS THAN(MAXVALUE)
) STORAGE(NOBRANCH);
执行成功, 执行耗时65毫秒. 执行号:2553
影响了0条记录
1条语句执行成功
堆表果然百无禁忌，这样都行！
维护分区表
增加分区
参看表T_R1，DM支持增加新分区到最后一个分区后面。如果要在表的开始或者中间增加分区，需要使用SPLIT PATITION语句。
对于LIST分区，增加的分区离散值不能已存在于现有的某个分区中。
只能对范围分区、LIST分区增加分区，哈希分区不可以。并且新增分区不会影响分区索引，只影响主表的分区信息。
删除分区
只能对范围分区和LIST分区进行删除分区操作，哈希分区不可以，同样也只是更新主表分区信息，其它不受影响。
总共1个语句正依次执行...
[执行语句1]:
alter TABLE SYSDBA.T_R1 drop PARTITION PN;
执行成功, 执行耗时68毫秒. 执行号:2598
影响了0条记录
1条语句执行成功
合并分区
总共1个语句正依次执行...
[执行语句1]:
alter TABLE SYSDBA.T_R4 MERGE PARTITIONS P2,P3 INTO PARTITION P4;
执行成功, 执行耗时57毫秒. 执行号:2611
影响了0条记录
1条语句执行成功
只有范围分区可以合并，并且需要为两个相邻的分区，且只能合并到新分区，不能并入已存在的其它分区。合并分区会导致数据重组和分区索引重建，所以合并分区操作会比较耗时，主要取决于数据量大小。一般只有在分区I/O极度不均衡时才考虑合并分区。
拆分分区
将一个分区拆分成两个，当一个分区过大，以至于查询性能低下，维护成本过高时，考虑拆分分区。还可以通过拆分重新划分I/O负载。与合并一样，仅范围分区支持拆分操作。也同样比较耗时，原因同上。
拆分前
总共1个语句正依次执行...
[执行语句1]:
alter TABLE T_R1 SPLIT PARTITION P1 AT(51) INTO (PARTITION P4,PARTITION P5);
执行成功, 执行耗时110毫秒. 执行号:2708
影响了0条记录
1条语句执行成功
拆分后
原P1分区中，ID为1~100，拆分后，P1分区消失，1~50为P4分区，51~100为P5分区。
交换分区
应用场景一般为分区表的历史数据卸载，例如：按年度分区的库存流水账表，要卸载2015年的数据，则可以将2015年对应的分区数据交换到同样表结构的备份表中，再drop掉2015年分区。
交换分区操作其实属于数据字典操作，几乎不涉及IO，所以效率非常高，但是要在系统设计初期就要考虑好分区方案和历史数据卸载方案。
--交换分区
--将T_R1表P2分区中的数据交换到T_R1_P2_BAK表，作为历史数据备份
--创建备份表
create TABLE T_R1_P2_BAK(ID int,NAME VARCHAR(20));
alter TABLE SYSDBA.T_R1 EXCHANGE PARTITION P2 WITH TABLE SYSDBA.T_R1_P2_BAK;
select * from SYSDBA.T_R1_P2_BAK;
