达梦数据库分区

达梦数据库分区

分区是指将表、索引等数据库对象划分为较小的可管理片段的技术，一个表被分区后，对表的查询操作可以局限于某个分区进行，而不是整个表，这样可以大大提高查询速度，在分区表中，子表跟主表具有相同的逻辑结构，主表本身不存储数据，所有数据只存储在子表中，从而实现不同分区的完全独立性，可满足企业高可用性、均衡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;