1.概念
达梦数据库支持三种分区
1.范围分区
2.hash分区
3.list 分区
4.多级分区：文档描述支持上述三种分区的任意组合
1.1.分区常见操作
创建一个大的基础的表用与表的分区测试,
create table phonedetail2 (
name varchar(50), --客户名
sex varchar(50), --性别
xiaofei int, --消费额
Province varchar(50), --省
City varchar(50), --市
District varchar(50), --区
xiaofei_date date
);

insert into phonedetail (name,sex,xiaofei,Province,City,District,xiaofei_date) values (‘张1’,‘男’,10,‘北京’,‘北京市’,‘丰台区’,‘2000-01-01 10:21:00’);
insert into phonedetail (name,sex,xiaofei,Province,City,District,xiaofei_date) values (‘张2’,‘女’,20,‘北京’,‘北京市’,‘顺义区’,‘2000-02-01 10:21:00’);
insert into phonedetail (name,sex,xiaofei,Province,City,District,xiaofei_date) values (‘张3’,‘男’,30,‘北京’,‘北京市’,‘通州区’,‘2000-03-01 10:21:00’);
insert into phonedetail (name,sex,xiaofei,Province,City,District,xiaofei_date) values (‘张4’,‘女’,40,‘北京’,‘北京市’,‘昌平区’,‘2000-04-01 10:21:00’);
insert into phonedetail (name,sex,xiaofei,Province,City,District,xiaofei_date) values (‘张5’,‘男’,50,‘新疆’,‘乌鲁木齐市’,‘新市区’,‘2000-05-01 10:21:00’);
insert into phonedetail (name,sex,xiaofei,Province,City,District,xiaofei_date) values (‘张6’,‘男’,60,‘新疆’,‘乌鲁木齐市’,‘米东区’,‘2001-06-01 10:21:00’);
insert into phonedetail (name,sex,xiaofei,Province,City,District,xiaofei_date) values (‘张7’,‘女’,70,‘新疆’,‘克拉玛依市’,‘独山子区’,‘2002-02-01 10:21:00’);
insert into phonedetail (name,sex,xiaofei,Province,City,District,xiaofei_date) values (‘张8’,‘女’,80,‘新疆’,‘克拉玛依市’,‘白碱滩区’,‘2003-02-01 10:21:00’);
insert into phonedetail (name,sex,xiaofei,Province,City,District,xiaofei_date) values (‘张9’,‘男’,90,‘新疆’,‘吐鲁番市’,‘高昌区’,‘2004-03-01 10:21:00’);
insert into phonedetail (name,sex,xiaofei,Province,City,District,xiaofei_date) values (‘张10’,‘女’,100,‘新疆’,‘哈密市’,‘伊州区’,‘2005-04-01 10:21:00’);

2.分区操作
2.1.创建分区
需求：根据消费日期进行分区每年一个
create table phonedetail (
name varchar(50), --客户名
sex varchar(50), --性别
xiaofei int, --消费额
Province varchar(50), --省
City varchar(50), --市
District varchar(50), --区
xiaofei_date date)
partition by RANGE(xiaofei_date)
(
partition p1 values less than (‘2000-01-01’),
partition p2 values less than (‘2001-01-01’),
partition p3 values less than (‘2002-01-01’),
partition p4 values less than (‘2003-01-01’),
partition p5 values less than (‘2004-01-01’),
partition p6 values less than (‘2005-01-01’),
partition p_max values less than (MAXVALUE)
);
select * from phonedetail PARTITION(p3);

2.2.创建多级子分区
需求1： 创建一个二级分区
先创建一个根据消费日期分类的范围分区
在创建一个根据省进行的list 分区
create table phonedetail (
name varchar(50), --客户名
sex varchar(50), --性别
xiaofei int, --消费额
Province varchar(50), --省
City varchar(50), --市
District varchar(50), --区
xiaofei_date date)
partition by RANGE(xiaofei_date)
SUBPARTITION by list(Province) SUBPARTITION TEMPLATE(
SUBPARTITION p1_1 values (‘北京’),
SUBPARTITION p1_2 values (‘新疆’),
SUBPARTITION p1_3 values (DEFAULT）)
(
PARTITION p1 values less than (‘2000-01-01’),
PARTITION p2 values less than (‘2001-01-01’),
PARTITION p3 values less than (‘2002-01-01’),
PARTITION p4 values less than (‘2003-01-01’),
PARTITION p5 values less than (‘2004-01-01’),
PARTITION p6 values less than (‘2005-01-01’),
PARTITION p_max values less than (MAXVALUE)
)

需求2：创建一个三级分区
先创建一个根据消费日期分类的范围分区
在创建一个根据省进行的list 分区
在创建一个根据区进行hash
create table phonedetail (
name varchar(50), --客户名
sex varchar(50), --性别
xiaofei int, --消费额
Province varchar(50), --省
City varchar(50), --市
District varchar(50), --区
xiaofei_date date)
partition by RANGE(xiaofei_date)
SUBPARTITION by list(Province) SUBPARTITION TEMPLATE(
SUBPARTITION list_p1 values (‘北京’),
SUBPARTITION list_p2 values (‘新疆’),
SUBPARTITION list_p3 values (DEFAULT)),
SUBPARTITION by HASH(District) SUBPARTITION TEMPLATE(
SUBPARTITION hash_p1,
SUBPARTITION hash_p2,
SUBPARTITION hash_p3,
SUBPARTITION hash_p4)
(
PARTITION RANGE_p1 values less than (‘2000-01-01’),
PARTITION RANGE_p2 values less than (‘2001-01-01’),
PARTITION RANGE_p3 values less than (‘2002-01-01’),
PARTITION RANGE_p4 values less than (‘2003-01-01’),
PARTITION RANGE_p5 values less than (‘2004-01-01’),
PARTITION RANGEt_p6 values less than (‘2005-01-01’),
PARTITION RANGE_p_max values less than (MAXVALUE)
);

2.3.添加分区
1.如果没有maxvalue
alter table PHONEDETAIL add PARTITION p7 VALUES LESS THAN (‘2006-01-01’)

2.如果原分区表有maxvalue
那就只能进行分裂了
若对MAXVALUE分区进行分割，前面几个分区不能申明MAXVALUE范围，最后一个分区会继承MAXVALUE分区范围。
alter table PHONEDETAIL SPLIT PARTITION p_max INTO ( PARTITION p7 VALUES LESS THAN (‘2006-01-01’),PARTITION p_max )

2.4.删除分区
alter table PHONEDETAIL DROP PARTITION p1;

2.5.分裂分区
alter table PHONEDETAIL SPLIT PARTITION p_max INTO ( PARTITION p7 VALUES LESS THAN (‘2006-01-01’),PARTITION p_max )

2.6.合并分区
合并分区不能作用于哈希分区上。
alter table PHONEDETAIL MERGE PARTITIONS p1 ,p2 INTO PARTITION p1_2 ;

2.7.分区表置换
alter TABLE PHONEDETAIL EXCHANGE PARTITION p3 WITH TABLE PHONEDETAIL2;

2.8.全局分区索引
//其次，指定GLOBAL创建全局索引
create index idx1 on t1(c2) GLOBAL;
2.9.局部分区索引
缺省GLOBAL创建局部索引idx2 create index idx2 on t1(c3);

https://eco.dameng.com