达梦分区表简介
分区表的目的：把一张超大的表，逻辑拆分出多张小表，分散IO，方便管理，提高数据查询和DML操作的效率
分区表的优势：
- 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。
- 增加了可用性：若某个分区出现故障，表在其他分区的数据仍能查询。
- 维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可减少恢复时间。
- 均衡IO：可以把不同的分区映射到磁盘以均衡IO。
水平分区（按照记录进行分区）类型：
- 范围分区
- 列表分区
- 哈希分区
- 组合分区/多级分区表
在创建表的语法中，使用 partition 子句指定分区方式和分区列，以及分区的名字等信息，即可创建分区表。
而分区子表可以指定 storage 子句，设置子表的存储属性，如所属表空间等；如果不指定，则继承分区主表的存储特性及表的其他属性。同时，支持多级分区表。
水平分区表的 ROWID 与其主表属性一致，且每个子表的 ROWID 都是从 1 开始增长，但是最终返回前，ROWID 的高字节会补充上子表序号。
范围分区
每一个分区都必须有一个 VALUES LESS THAN 子句，它指定了一个不包括在该分区中的上限值。分区键的任何值等于或者大于这个上限值的记录都会被加入到下一个高一些的分区中。
所有分区，除了第一个，都会有一个隐式的下限值，这个值就是此分区的前一个分区的上限值。
在最高的分区中，MAXVALUE 被定义。MAXVALUE 代表了一个不确定的值。这个值高于其它分区中的任何分区键的值，也可以理解为高于任何分区中指定的 VALUE LESS THAN 的值，同时包括空值。
- 要求分区列是数字类型或是日期类型
PARTITION BY RANGE 范围分区关键字
VALUES LESS THAN (NUMBER)，键值小于 number 范围的记录。可以使用使用关键字 MAXVALUE 来创建一个分区存放所有范围外值的记录
create table "TEST"."T1"
(
"id" NUMBER(10),
"name" VARCHAR2(20)
)
PARTITION BY RANGE("id")
(PARTITION p1 VALUES LESS THAN (100), --ID<100存放在P1子分区表
PARTITION p2 VALUES LESS THAN (200), --100<=ID<200存放在P2子分区表
PARTITION p3 VALUES LESS THAN (300) --200<=ID<300存放在P3子分区表
);
--插入数据
begin
for i in 1..299 loop
insert into "TEST"."T1" values(i, 'SSS'||i);
commit;
end loop;
end;
--添加一个分区
alter table "TEST"."T1"
add partition pn values less than(maxvalue);--当值不满足所有范围的记录都存放在该子分区表内
insert into test."T1" values (300,'SSS789');
insert into test."T1" values (800,'SSS800');
commit;
SQL> select * from test.t1 PARTITION(pn);
行号 id name
---------- --- ------
1 300 SSS789
2 800 SSS800
列表分区
适合字符串类型 ![[第15章、管理分区表和分区索引#^0f0c1e]]
PARTITION BY LIST 分区列表关键字段
PARTITION VALUES
SQL> CREATE TABLE TEST.T2(SALES_ID INT,SALEMAN CHAR(20),SALEDATE DATE, CITY CHAR(10))
PARTITION BY LIST (CITY)
(PARTITION P1 VALUES('北京','天津','哈尔滨','青岛'),
PARTITION P2 VALUES('上海','南京','杭州'),
PARTITION P3 VALUES('武汉','长沙','合肥'),
PARTITION P4 VALUES('广州','深圳','福建'));
警告: 列表分区未包含DEFAULT,可能无法定位到分区--提示无default值
操作已执行
已用时间: 8.343(毫秒). 执行号:713.
insert into test.t2 values(3,'CCCC','2022-05-05','南京');--插入记录满足P2分区
commit;
SQL> select * from test.t2 PARTITION(p2);--查询确认p2分区内记录是否插入
行号 SALES_ID SALEMAN SALEDATE CITY
---------- ----------- -------------------- ---------- ----------
1 3 CCCC 2022-05-05 南京
已用时间: 1.093(毫秒). 执行号:716.
SQL> insert into test.t2 values(4,'DDDD','2022-04-18','西安');
insert into test.t2 values(4,'DDDD','2022-04-18','西安');
[-2731]:没有找到合适的分区.--由于无default值，西安不在原来list内。无法插入分区
已用时间: 0.447(毫秒). 执行号:0.
--添加default值分区
alter table test.T2 add partition PN values(default);
SQL> insert into test.t2 values(4,'DDDD','2022-04-18','西安');
影响行数 1
--再次插入成功
已用时间: 0.924(毫秒). 执行号:719.
commit;
--确认插入记录是否在pn分区内。
SQL> select * from test.t2 PARTITION(pn);
行号 SALES_ID SALEMAN SALEDATE CITY
---------- ----------- -------------------- ---------- ----------
1 4 DDDD 2022-04-18 西安
已用时间: 0.802(毫秒). 执行号:720.
哈希分区
将分区列的值进行 hash 运算，然后将数据平均分配各个子分区，存储数据非常快，取数据慢，如果 hash 分区不指定分区表名，那就通过指定的哈希分区数来创建。
DM 哈希分区提供了一种在指定数量的分区中均等地划分数据的方法，基于分区键的散列值将行映射到分区中。当用户向表中写入数据时，数据库服务器将根据一个哈希函数对数据进行计算，把数据均匀地分布在各个分区中。在哈希分区中，用户无法预测数据将被写入哪个分区中。
分区表名统一使用DMHASHPART+分区号（从0开始）作为分区名。
create TABLE test.t4
(id int,name varchar2(10))
partition by HASH(id)
(PARTITION "01",PARTITION "02",PARTITION "03",PARTITION "04");
create TABLE test.t_hash
(id int,name varchar2(10))
partition by HASH(id) PARTITIONS 5;
begin
for i in 1..10000 loop
insert into test.T_HASH VALUES(i,'RRRR'||i);
end loop;
commit;
end;
组合分区
range-list
range-hash
range-range
list-list
hash-hash
list-range
DM支持最多8层分区
list-range
create table test.sales_sum(sale_id int,sale_name VARCHAR(20),sale_date DATE,city char(10))
PARTITION BY LIST(city)
SUBPARTITION by RANGE(sale_date)
SUBPARTITION TEMPLATE(
SUBPARTITION p11 VALUES LESS THAN('2012-04-01'),
SUBPARTITION p12 VALUES LESS THAN('2013-04-01'),
SUBPARTITION p13 VALUES LESS THAN(MAXVALUE))
(partition P1 values('北京','天津','哈尔滨','青岛')
(subpartition p1_1 values less than('2012-08-01'),
subpartition p1_2 values less than('2013-08-01'),
subpartition p1_3 values less than(maxvalue)),
partition P2 values('上海','南京','杭州'),
partition P3 values('武汉','长沙','合肥'),
partition P4 values('广州','深圳','福建'),
partition p5 values(default));
insert into test.SALES_SUM VALUES(1,'AAAA','2012-05-01','北京');
--由于使用了自己定义，不能使用模板，存放在P1_P1_1
insert into test.SALES_SUM VALUES(1,'cccc','2012-05-01','武汉');
--由于P2没有自己定义，采用模板，存放在P3_P12里面
SQL> select * from test.SALES_SUM_p1_p1_1;
行号 SALE_ID SALE_NAME SALE_DATE CITY
---------- ----------- --------- ---------- ----------
1 1 AAAA 2012-05-01 北京
已用时间: 0.345(毫秒). 执行号:2702.
SQL> select * from test.SALES_SUM_p3_p12;
行号 SALE_ID SALE_NAME SALE_DATE CITY
---------- ----------- --------- ---------- ----------
1 1 AAAA 2012-05-01 武汉
已用时间: 0.426(毫秒). 执行号:2703.
间隔分区
一种的范围分区，是范围分区的扩展。在范围分区上增加一个间隔表达式就是间隔分区。‘
间隔函数NUMTOYMINTERVAL(N,INTERVAL_UNIT)
N是间隔数量
INTERVAL_UNIT 间隔单位
select sysdate + numtoyminterval(1,'year');
select sysdate + numtoyminterval(1,'month');
select sysdate + numtodsinterval(1,'day');
create TABLE test.t6(employee_id int,employess_name VARCHAR(20),bir date)
PARTITION BY RANGE(bir)
INTERVAL(numtoyminterval(1,'year'))
(PARTITION p1990 VALUES LESS THAN(to_date('1991-01-01','yyyy-mm-dd')),
PARTITION p1991 VALUES LESS THAN(to_date('1992-01-01','yyyy-mm-dd')),
PARTITION p1992 VALUES LESS THAN(to_date('1993-01-01','yyyy-mm-dd')));
insert into test.T6 VALUES(1,'eee','1989-01-01');
insert into test.T6 VALUES(2,'fff','1991-01-01');
--这条记录会自动创建一个新的分区，分区原则是根据间隔函数原则建立分区
insert into test.T6 VALUES(3,'ggg','1994-01-01');
--注意，以下查询都会自动创建新分区。
insert into test.T6 VALUES(4,'www','1998-01-01');
insert into test.T6 VALUES(5,'ppp','1996-01-01');
--自动创建的分区由数据库自动维护，只会在数据字典中体现，表定义不会出现这些分区。
SQL> select table_name,DBA_TAB_PARTITIONS.PARTITION_NAME,DBA_TAB_PARTITIONS.HIGH_VALUE from SYS.DBA_TAB_PARTITIONS
where DBA_TAB_PARTITIONS.TABLE_NAME='T6';
行号 TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- ---------- -------------- ----------------
1 T6 SYS_P1155_1163 DATE'1997-01-01'
2 T6 SYS_P1155_1161 DATE'1999-01-01'
3 T6 SYS_P1155_1159 DATE'1995-01-01'
4 T6 P1992 DATE'1993-01-01'
5 T6 P1991 DATE'1992-01-01'
6 T6 P1990 DATE'1991-01-01'
6 rows got
已用时间: 13.993(毫秒). 执行号:2708.
分区表维护
数据字典：DBA_TAB_PARTITION
- 增加分区
alter TABLE TEST.T1 add partition pn values less than(maxvalue);
- 删除分区(注意；删除分区前请确认好，并做好备份。)
alter table test.t1 drop partition pn;
- 合并分区 (合并的分区要相邻).
alter table test.t1 merge partitions p2,p3 into partition p2_3;
- 分割分区
alter table test.t1 split partition P2_3 at(200) into (partition p2,partition p3);
- 交换分区
将分区表和普通表数据进行交换。并且分区表和普通表的结构要完全一致，包括字段类型及约束情况。
注意：交换时，会普通表的所有数据全部交换过来，包括不属于这个分区的数据也一起交换过来, 所以我们在交换分区的时候，要把普通表的数据先整理好。否则查询会找不到记录！
ALTER TABLE TEST.T1 EXCHANGE PARTITION P1 WITH TABLE TEST.T10;
- 如何把非分区表转换成分区表
1）把非分区表的数据导出来。
2）建立一个和非分区表结构一样的分区表
3）将数据导入到分区表中。
--创建普通表
CREATE TABLE TEST.T20(ID INT);
--插入200000记录
BEGIN
FOR I IN 1..200000 LOOP
INSERT INTO TEST.T20 VALUES(I);
END LOOP;
COMMIT;
END;
select count(*) from test.t20;
#逻辑备份t20
[dmdba@dmdwmonitor dump]$ /dm8/bin/dexp test/dameng123 file=/dm8/dump/dm20.dmp tables=t20
dexp V8
----- [2022-10-01 22:54:49]导出表：T20 -----
导出模式下的对象权限...
表T20导出结束，共导出 200000 行数据
整个导出过程共花费 0.350 s
成功终止导出, 没有出现警告
--删除普通表T20，创建分区表T20，准备将逻辑备份出的200000记录重新写回分区表T20重新导入。
SQL> drop table t20;
SQL>create table test.t20(id int)
partition by range(id)
(partition p1 values less than(1000),
partition p2 values less than(2000),
partition p3 values less than(3000),
partition p4 values less than(4000),
partition p5 values less than(5000),
partition p6 values less than(6000),
partition p7 values less than(7000),
partition p8 values less than(8000),
partition pn values less than(maxvalue));
#由于表T20已经存在，所以需要添加ignore=y选项来忽略表已经存在的错误。
[dmdba@dmdwmonitor dump]$ /dm8/bin/dimp test/dameng123 file=/dm8/dump/dm20.dmp tables=t20 ignore=y
dimp V8
本地编码：PG_UTF8, 导入文件编码：PG_GB18030
----- [2022-10-01 23:02:13]导入表：T20 -----
导入表 T20 的数据：200000 行被处理
整个导入过程共花费 8.723 s
成功终止导入, 没有出现警告
版权声明：
本站所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自
数据生活，生活数据！
喜欢就支持一下吧