达梦数据库分区
达梦数据库分区
分区是指将表、索引等数据库对象划分为较小的可管理片段的技术，一个表被分区后，对表的查询操作可以局限于某个分区进行，而不是整个表，这样可以大大提高查询速度，在分区表中，子表跟主表具有相同的逻辑结构，主表本身不存储数据，所有数据只存储在子表中，从而实现不同分区的完全独立性，可满足企业高可用性、 均衡IO、降低维护成本、提高查询性能的要求。分区主要包括范围分区、哈希分区、列表分区和复合分区。按时间分区是现实生产中最主要的分区方式，本文主要讲解这种分区方式。
创建范围分区表
create table PART(tdate datetime,city varchar(50),income number(10,2))
PARTITION BY RANGE(tdate)
( PARTITION p2016 VALUES LESS THAN ('2017-01-01'),
PARTITION p2017 VALUES LESS THAN ('2018-01-01'),
PARTITION p2018 VALUES LESS THAN ('2019-01-01'),
PARTITION p2019 VALUES LESS THAN ('2020-01-01'),
PARTITION p_other VALUES LESS THAN (MAXVALUE)
);
创建存储过程proc_part
CREATE OR REPLACE PROCEDURE proc_part AS
DECLARE
StartDate DATE:=DATE '2000-01-01';
begin
for i in 1..11000 loop
INSERT INTO PART (PART.TDATE,PART.CITY,PART.income)
SELECT
StartDate+i,
CITY.CITY_NAME,
convert(number(10,2),1000000*RAND())
from "DMHR"."CITY";
end loop;
commit;
end;
调用proc_part
call PROC_PART;
查看表分区数据
查看分区
select partition_name from dba_tab_partitions where table_name='PART';
交换分区
1新建交换表，表结构和分区表字段相同
create table PART2 as select * from PART where 1=2;
2 交换分区
ALTER TABLE PART EXCHANGE PARTITION p_other WITH TABLE PART2;
可以看到p_other分区数据已经到新建交换表中了
删除分区
alter table PART drop partition p_other
增加分区
alter table PART add partition p2020 values less than('2021-01-01');
合并分区
alter table PART merge partitions p2016,p2017 into partition p20116_17;
拆分分区
alter table PART split partition p20116_17 at ('2017-01-01') into (partition p2016,partition p2017);
定时自动新增表分区
创建分区表
create table PART(tdate datetime,city varchar(50),income number(10,2))
PARTITION BY RANGE(tdate)
( PARTITION p_2016 VALUES LESS THAN ('2017-01-01'),
PARTITION p_2017 VALUES LESS THAN ('2018-01-01'),
PARTITION p_2018 VALUES LESS THAN ('2019-01-01'),
PARTITION p_2019 VALUES LESS THAN ('2020-01-01'),
PARTITION p_2020_01 VALUES LESS THAN ('2020-02-01'),
PARTITION p_2020_02 VALUES LESS THAN ('2020-03-01'),
PARTITION p_2020_03 VALUES LESS THAN ('2020-04-01'),
PARTITION p_2020_04 VALUES LESS THAN ('2020-05-01'),
PARTITION p_2020_05 VALUES LESS THAN ('2020-06-01'),
PARTITION p_2999 VALUES LESS THAN (MAXVALUE)
);
查看表分区信息
select partition_name from dba_tab_partitions where table_name='PART' order by 1 desc
按月份增加分区存储过程
create or replace procedure addpartition(tname varchar(50)) is
declare
highvalue varchar(50);
partition_name varchar(50);
month_name varchar(2);
year_name varchar(4);
sSql varchar(2000);
dSql varchar(2000);
aSql varchar(2000);
begin
select substr(max(high_value),10,10) into highvalue from dba_tab_partitions where table_name=sTable_name and table_owner='SYSDBA' and HIGH_VALUE like 'DATETIME%';
year_name = datepart(year,highvalue);
if datepart(month,highvalue)<10 then
month_name = '0' || datepart(month,highvalue);
else
month_name = datepart(month,highvalue);
end if;
partition_name = 'p_' || year_name ||'_'|| month_name;
highvalue=dateadd(MM,1,highvalue);
year_name = datepart(year,highvalue);
month_name=datepart(month,highvalue);
dSql := 'alter table '|| sTable_name || ' drop partition p_2999';
sSql := 'alter table '|| sTable_name || ' add partition ' || partition_name || ' values less than(DATETIME''' || year_name || '-' || month_name || '-01 00:00:00'')';
aSql := 'alter table '|| sTable_name || ' add partition p_2999 values less than (MAXVALUE)';
execute immediate dSql;
execute immediate sSql;
execute immediate aSql;
end;
设置job，定时调用调用该存储过程（过程略）
call addpartition('PART');
查看结果
select partition_name from dba_tab_partitions where table_name='PART' order by 1 desc
