表分区是数据库性能优化、数据运维的常见手段，需要在系统设计或表结构设计初期便要考虑好使用场景，评估性能校准及数据卸载方案，本文介绍DM达梦8分区表类型、使用及维护方法。

分区表类型：水平分区、垂直分区

水平分区：范围、哈希、列表三种方式。另外，多集分区表是将三种方式组合、嵌套使用，形成多集分区表

分区的好处：可以将大表或索引分割成独立的、可存储在不同存储介质上的小的单位，提升查询性能、均衡IO、高可用性，降低维护成本。

# 分区概念

将大表或索引等数据库对象，分割成较小的可管理片段，每个片段被称为分区（表或索引）。

# 分区方法

###### 范围水平分区

Range：对表中某列上值的范围进行分区，根据某个值的范围决定将该数据存储在哪个分区上；

###### 哈希水平分区

hash：通过指定分区编号来均匀分布数据，通过在IO设备上进行散列分区，使得这些分区大小基本一致；

###### 列表水平分区

list：通过指定表中的某个列的离散值集，来确定应该存放在一起的数据；

###### 多级分区

以上分区方式的混合分级使用；

# 创建分区表

## 创建范围分区表

```sql
总共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条语句执行成功
```

```sql
select
        DBA_TABLES.TABLE_NAME,
        DBA_TABLES.PARTITIONED
from
        SYS.DBA_TABLES
WHERE
        DBA_TABLES.TABLE_NAME = 'T_R1';
```

![](http://img.blog.itpub.net/blog/2020/08/27/e61f805f73375c11.png?x-oss-process=style/bb)

查询表分区

```sql
select
        DBA_TAB_PARTITIONS.TABLE_NAME,
        DBA_TAB_PARTITIONS.PARTITION_NAME
from
        SYS.DBA_TAB_PARTITIONS
where
        DBA_TAB_PARTITIONS.TABLE_NAME = 'T_R1';
```

![](http://img.blog.itpub.net/blog/2020/08/27/f936733d4fd77ccf.png?x-oss-process=style/bb)

灌数据验证一下

```sql
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;
```

![](http://img.blog.itpub.net/blog/2020/08/27/3e5b51158552fc43.png?x-oss-process=style/bb)

```sql
select count(0) from SYSDBA.T_R1 PARTITION(P1);
```

![](http://img.blog.itpub.net/blog/2020/08/27/efecd8dd64ac4d4f.png?x-oss-process=style/bb)

![](http://img.blog.itpub.net/blog/2020/08/27/2b84163c95935e0a.png?x-oss-process=style/bb)

插入300，提示没有找到合适的分区

```sql
insert into SYSDBA.T_R1 VALUES(300,'AA300');
总共1个语句正依次执行...

[执行语句1]:
insert into SYSDBA.T_R1 VALUES(300,'AA300');
执行失败(语句1)
没有找到合适的分区

1条语句执行失败
```

查询分区最高值

```sql
select
        DBA_TAB_PARTITIONS.PARTITION_NAME,
        DBA_TAB_PARTITIONS.HIGH_VALUE
from
        SYS.DBA_TAB_PARTITIONS
where
        DBA_TAB_PARTITIONS.TABLE_NAME = 'T_R1';
```

![](http://img.blog.itpub.net/blog/2020/08/27/0b88dbed7b533e03.png?x-oss-process=style/bb)

新增分区

```sql
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;
```

![](http://img.blog.itpub.net/blog/2020/08/27/01e1b34d34982a60.png?x-oss-process=style/bb)

不同分区放在不同表空间

```sql
总共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条语句执行成功
```

创建水平分区堆表

```sql
总共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目前不允许将堆表的分区放在不同的表空间里。

解决方法：放在同一个表空间中

```sql
总共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条语句执行成功

```

## 创建列表分区表

列表分区表分区列适合字符型

```sql
总共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分区表

由于官方文档中提到哈希分区存数据快，但通过测试并没有发现哈希分区比普通表存数据快，相反还慢了不好，不知道这里官方文档所说的性能好，是与那种类型的表作对比。

```sql
总共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 ....)，则表示分区放在不同的表空间。

测试插入数据性能

```sql
总共1个语句正依次执行...

[执行语句1]:
BEGIN
	FOR I IN 0..1000 LOOP
		INSERT INTO SYSDBA.T_R6 VALUES(I);
		COMMIT;
	END LOOP;
END;
执行成功, 执行耗时258毫秒. 执行号:2340
影响了1条记录

1条语句执行成功
```

![](http://img.blog.itpub.net/blog/2020/08/27/d92be874047d3f13.png?x-oss-process=style/bb)

我高估了dm的性能，测试用例插入100w行，两分半还没执行完?

```sql
总共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行记录

```sql
总共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分区表，销售记录表，按照区域和时间做多级分区划分

```sql
总共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条语句执行成功
```

这样，就会按照区域和时间，将记录存储到对应的分区中，非常有利于大表数据拆分存储，提高查询性能。

![](http://img.blog.itpub.net/blog/2020/08/27/7a53384aea237c41.png?x-oss-process=style/bb)

可以看到P1子表被我们自定义了，P2按模板进行了子表划分；

![](http://img.blog.itpub.net/blog/2020/08/27/7a32c5bdeb0484c2.png?x-oss-process=style/bb)

![](http://img.blog.itpub.net/blog/2020/08/27/f4df90f61af317ba.png?x-oss-process=style/bb)

数据进入到了对应的表分区中。

达梦最多支持8级分区表。

# 分区表限制

1. 水平分区表（水平分区包括范围、哈希和列表三种方式）指定主键与唯一索引时，分区键必须都包含在主键和唯一约束中。

```sql
总共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条语句执行失败
```

```sql
总共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条语句执行成功
```

增加一个唯一索引列

```sql
总共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的联合主键

```sql
总共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条语句执行成功
```

创建成功，但是感觉这个方案很猥琐！

方法二：改为堆表

```sql
总共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条语句执行成功
```

创建成功。

```sql
总共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分区进行删除分区操作，哈希分区不可以，同样也只是更新主表分区信息，其它不受影响。

```sql
总共1个语句正依次执行...

[执行语句1]:
alter TABLE SYSDBA.T_R1 drop PARTITION PN;
执行成功, 执行耗时68毫秒. 执行号:2598
影响了0条记录

1条语句执行成功
```

## 合并分区

```sql
总共1个语句正依次执行...

[执行语句1]:
alter TABLE SYSDBA.T_R4 MERGE PARTITIONS P2,P3 INTO PARTITION P4;
执行成功, 执行耗时57毫秒. 执行号:2611
影响了0条记录

1条语句执行成功
```

只有范围分区可以合并，并且需要为两个相邻的分区，且只能合并到新分区，不能并入已存在的其它分区。合并分区会导致数据重组和分区索引重建，所以合并分区操作会比较耗时，主要取决于数据量大小。一般只有在分区I/O极度不均衡时才考虑合并分区。

## 拆分分区

将一个分区拆分成两个，当一个分区过大，以至于查询性能低下，维护成本过高时，考虑拆分分区。还可以通过拆分重新划分I/O负载。与合并一样，仅范围分区支持拆分操作。也同样比较耗时，原因同上。

拆分前

![](http://img.blog.itpub.net/blog/2020/08/27/65fa44b6099554cc.png?x-oss-process=style/bb)

```sql
总共1个语句正依次执行...

[执行语句1]:
alter TABLE T_R1 SPLIT PARTITION P1 AT(51) INTO (PARTITION P4,PARTITION P5);
执行成功, 执行耗时110毫秒. 执行号:2708
影响了0条记录

1条语句执行成功
```

拆分后

![](http://img.blog.itpub.net/blog/2020/08/27/9575f97d4d1529f8.png?x-oss-process=style/bb)

![](http://img.blog.itpub.net/blog/2020/08/27/e8059ac047c89deb.png?x-oss-process=style/bb)

![](http://img.blog.itpub.net/blog/2020/08/27/643a41f5414c8e47.png?x-oss-process=style/bb)

原P1分区中，ID为1~100，拆分后，P1分区消失，1~50为P4分区，51~100为P5分区。

## 交换分区

应用场景一般为分区表的历史数据卸载，例如：按年度分区的库存流水账表，要卸载2015年的数据，则可以将2015年对应的分区数据交换到同样表结构的备份表中，再drop掉2015年分区。

交换分区操作其实属于数据字典操作，几乎不涉及IO，所以效率非常高，但是要在系统设计初期就要考虑好分区方案和历史数据卸载方案。

```sql
--交换分区
--将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;
```

![](http://img.blog.itpub.net/blog/2020/08/27/fbeb2c1014b4503d.png?x-oss-process=style/bb)

ITPub博客

DM达梦8分区表使用及维护方法

最新文章