【DM】达梦数据库中分区表的创建及查询操作

一、摘要

达梦数据库对表是进行水平分区的：水平分区的方式有以下几种：

• 范围分区表
• LIST分区表
• 哈希分区表
• 多级分区表

二、演示

范围分区表

按商品价格范围，将商品表分为四个子表

分区列	范围区间
PRICE	<10
	>=10&&<100
	>=100&&<1000
	>=1000

建表

CREATE TABLE HA.INFO(
	NAME VARCHAR(50),
	PRICE INT
)
PARTITION BY RANGE(PRICE)(
	PARTITION P1 VALUES LESS THAN (10),
	PARTITION P2 VALUES LESS THAN (100),
	PARTITION P3 VALUES LESS THAN (1000),
	PARTITION P4 VALUES LESS THAN (MAXVALUE)
);
COMMIT;

插入数据

insert into HA.INFO values('铅笔',1);
insert into HA.INFO values('笔记本',22);
insert into HA.INFO values('蓝牙耳机',868);
insert into HA.INFO values('电脑',4599);
insert into HA.INFO values('牙刷',8);
insert into HA.INFO values('水杯',15);

查询

SQL> SELECT * FROM HA.INFO PARTITION(P1);

行号     NAME   PRICE
---------- ------ -----------
1          铅笔 1
2          牙刷 8

已用时间: 0.605(毫秒). 执行号:100.
SQL>  SELECT * FROM HA.INFO PARTITION(P2);

行号     NAME      PRICE
---------- --------- -----------
1          笔记本 22
2          水杯    15

已用时间: 1.531(毫秒). 执行号:101.
SQL>  SELECT * FROM HA.INFO PARTITION(P3);

行号     NAME         PRICE
---------- ------------ -----------
1          蓝牙耳机 868

已用时间: 0.866(毫秒). 执行号:102.
SQL>  SELECT * FROM HA.INFO PARTITION(P4);

行号     NAME   PRICE
---------- ------ -----------
1          电脑 4599

已用时间: 0.839(毫秒). 执行号:103.

LIST分区表

如果分区列是不连续的，那么范围分区将不再适用，这时候如果分区列上有一些固定的值，那么可以进行列表分区。

建表

CREATE TABLE HA.CITYS(
	CITY VARCHAR(50),
	PEOPLE VARCHAR(50)
	)
PARTITION BY LIST(CITY)(
	PARTITION P1 VALUES('北京','天津'),
	PARTITION P2 VALUES('郑州','武汉')
	);
COMMIT;

插入数据

INSERT INTO HA.CITYS VALUES('北京','赵剑');
INSERT INTO HA.CITYS VALUES('天津','刘华');
INSERT INTO HA.CITYS VALUES('武汉','吴枫');
INSERT INTO HA.CITYS VALUES('郑州','王鹏');
COMMIT;

查询

SQL> SELECT * FROM HA.CITYS PARTITION(P1);

行号     CITY   PEOPLE
---------- ------ ------
1          北京 赵剑
2          天津 刘华

已用时间: 41.528(毫秒). 执行号:16.
SQL> SELECT * FROM HA.CITYS PARTITION(P2);

行号     CITY   PEOPLE
---------- ------ ------
1          武汉 吴枫
2          郑州 王鹏

已用时间: 0.787(毫秒). 执行号:17.

如果CITY不在分区里面怎么办

SQL> insert into ha.citys values('上海','李娇');
insert into ha.citys values('上海','李娇');
[-2731]:没有找到合适的分区.
已用时间: 3.747(毫秒). 执行号:0.
SQL>

这种情况是根本无法插入数据的。

哈希分区表

哈希分区表是针对上面这种情况而设计的，有时候，分区列是离散的，且值的范围也不固定，这时候就要用到哈希分区表了。它在指定数量的分区中均等地划分数据。

建表

CREATE TABLE HA.HASHCITYS(
	CITY VARCHAR(50),
	PEOPLE VARCHAR(50)
	)
PARTITION BY HASH(CITY)(
	PARTITION P1,
	PARTITION P2 
	);
COMMIT;

插入数据

INSERT INTO HA.HASHCITYS VALUES('北京','赵剑');
INSERT INTO HA.HASHCITYS VALUES('天津','刘华');
INSERT INTO HA.HASHCITYS VALUES('武汉','吴枫');
INSERT INTO HA.HASHCITYS VALUES('郑州','王鹏');
COMMIT;

查询

SQL> SELECT * FROM HA.HASHCITYS PARTITION(P1);

行号     CITY   PEOPLE
---------- ------ ------
1          武汉 吴枫
2          郑州 王鹏

已用时间: 0.706(毫秒). 执行号:26.
SQL> SELECT * FROM HA.HASHCITYS PARTITION(P2);

行号     CITY   PEOPLE
---------- ------ ------
1          北京 赵剑
2          天津 刘华

已用时间: 0.765(毫秒). 执行号:27.

这部分和列表分区是一样的。

插入数据

SQL> INSERT INTO HA.HASHCITYS VALUES('上海','李娇');
影响行数 1

已用时间: 0.805(毫秒). 执行号:28.
SQL> COMMIT;
操作已执行
已用时间: 2.454(毫秒). 执行号:29.

这里却能插入成功

查询

SQL> SELECT * FROM HA.HASHCITYS PARTITION(P2);

行号     CITY   PEOPLE
---------- ------ ------
1          北京 赵剑
2          天津 刘华
3          上海 李娇

已用时间: 0.447(毫秒). 执行号:32.

可以看到这条记录被自动划分到了P2分区当中。

多级分区表

有时候一次分区并不能准确地对数据进行分类，这时候就需要进行多级分区。

下面做一个三级分区。第一级：基于年级的列表分区。第二级：基于性别的列表分区。第三级：基于分数的范围分区。

建表

CREATE TABLE HA.STUDENTS(
    NAME VARCHAR(50),
	GRADE INT CHECK (GRADE IN (7,8,9)),
	SEX CHAR(2) CHECK (SEX IN ('男','女')),
	CORES INT CHECK (CORES>=0 AND CORES<=100)
)
PARTITION BY LIST(GRADE)
	SUBPARTITION BY LIST(SEX) SUBPARTITION TEMPLATE(
		SUBPARTITION S1 VALUES ('男'),
		SUBPARTITION S2 VALUES ('女')
		),
	SUBPARTITION BY RANGE(CORES) SUBPARTITION TEMPLATE(
		SUBPARTITION C1 VALUES LESS THAN (60),
		SUBPARTITION C2 VALUES LESS THAN (80),
		SUBPARTITION C3 VALUES LESS THAN (100)
		)
	(
	PARTITION G1 VALUES (7),
	PARTITION G2 VALUES (8),
	PARTITION G3 VALUES (9)
	);

插入数据

INSERT INTO HA.STUDENTS VALUES('林华',7,'男',59);
INSERT INTO HA.STUDENTS VALUES('刘烨',8,'男',69);
INSERT INTO HA.STUDENTS VALUES('赵剑',9,'男',81);
INSERT INTO HA.STUDENTS VALUES('刘蕾',7,'女',58);
INSERT INTO HA.STUDENTS VALUES('张月',8,'女',91);
INSERT INTO HA.STUDENTS VALUES('贺兰',9,'女',71);
INSERT INTO HA.STUDENTS VALUES('许昕',7,'女',75);
INSERT INTO HA.STUDENTS VALUES('秦楠',7,'男',58);
INSERT INTO HA.STUDENTS VALUES('叶秋',9,'男',88);
COMMIT;

查询

第一级

SQL> SELECT* FROM HA.STUDENTS PARTITION(G1);

行号     NAME   GRADE       SEX CORES
---------- ------ ----------- --- -----------
1          林华 7           男 59
2          秦楠 7           男 58
3          刘蕾 7           女 58
4          许昕 7           女 75

已用时间: 1.365(毫秒). 执行号:46.
SQL> SELECT* FROM HA.STUDENTS PARTITION(G2);

行号     NAME   GRADE       SEX CORES
---------- ------ ----------- --- -----------
1          刘烨 8           男 69
2          张月 8           女 91

已用时间: 1.656(毫秒). 执行号:47.
SQL> SELECT* FROM HA.STUDENTS PARTITION(G3);

行号     NAME   GRADE       SEX CORES
---------- ------ ----------- --- -----------
1          赵剑 9           男 81
2          叶秋 9           男 88
3          贺兰 9           女 71

已用时间: 1.349(毫秒). 执行号:48.

第二级

SQL> SELECT * FROM HA.STUDENTS  SUBPARTITION(G1_S1);
S
行号     NAME   GRADE       SEX CORES
---------- ------ ----------- --- -----------
1          林华 7           男 59
2          秦楠 7           男 58

已用时间: 2.090(毫秒). 执行号:76.

以上语句的作用是查询7年级的所有男生的信息；

第三级

SQL> SELECT * FROM HA.STUDENTS  SUBPARTITION(G3_S2_C2);

行号     NAME   GRADE       SEX CORES
---------- ------ ----------- --- -----------
1          贺兰 9           女 71

已用时间: 1.108(毫秒). 执行号:82.

以上语句的作用是查询9年级所有60~70分段的女生的信息。

注意顺序不要反了，S2是二级分区而C2是三级分区，位置不可以互换。