达梦分区表

最新推荐文章于 2023-08-25 10:48:45 发布

yangeoooo

最新推荐文章于 2023-08-25 10:48:45 发布

阅读量1.5k

点赞数

分类专栏：达梦部署文章标签：数据库

本文链接：https://blog.csdn.net/yangeoooo/article/details/115003574

版权

达梦部署专栏收录该内容

7 篇文章 1 订阅

订阅专栏

分区表

创建分区表的限制

DM 默认类型的表（索引组织表）创建分区表的时候，主键列必须在分区范围内。
DM 的堆表上创建分区时，各个分区需要放在相同的表空间上。
如果表上存在聚集索引且索引键为主键，并希望各个分区放置在不同表空间上，优化 IO ，则必须在主键列中加入分区键，参考如下建表语句：

创建表空间

CREATE TABLESPACE tbs_p1 DATAFILE ‘/dm/data/DM1_NEW/p1.dbf’ SIZE 32;
CREATE TABLESPACE tbs_p2 DATAFILE ‘/dm/data/DM1_NEW/p2.dbf’ SIZE 32;
CREATE TABLESPACE tbs_p3 DATAFILE ‘/dm/data/DM1_NEW/p3.dbf’ SIZE 32;
CREATE TABLESPACE tbs_pmax DATAFILE ‘/dm/data/DM1_NEW/pmax.dbf’ SIZE 32;

创建分区表

CREATE TABLE dmhr.rp_emp
(
employee_id INT,
employee_name VARCHAR (20),
identity_card VARCHAR (18),
email VARCHAR (50) NOT NULL,
phone_num VARCHAR (20),
hire_date DATE NOT NULL,
job_id VARCHAR (10) NOT NULL,
salary INT,
commission_pct INT,
manager_id INT,
department_id INT,
CONSTRAINT pk_emp PRIMARY KEY (employee_id, hire_date),
CONSTRAINT emp_dept_fk1 FOREIGN KEY
(department_id)
REFERENCES dmhr.department (department_id),
CHECK (salary > 0)
)
PARTITION BY RANGE (hire_date)
(PARTITION p1 VALUES LESS THAN (‘2011-1-1’) TABLESPACE tbs_p1,
PARTITION p2 VALUES LESS THAN (‘2015-1-1’) TABLESPACE tbs_p2,
PARTITION p3 VALUES LESS THAN (‘2016-1-1’) TABLESPACE tbs_p3,
PARTITION pmax VALUES LESS THAN (MAXVALUE) TABLESPACE tbs_pmax)

创建各类分区表

范围分区

按照指定列的值所在的范围来创建分区。例如：以员工的入职时间为分区键创建范围分区，以年为间隔。示例语句如下：

//创建范围分区，以 hire_date 为分区键，同时增加 MAXVALUE 分区。
CREATE TABLE dmhr.rp_hiredt_emp
(
employee_id INT PRIMARY KEY,
employee_name VARCHAR (20),
identity_card VARCHAR (18),
email VARCHAR (50) NOT NULL,
phone_num VARCHAR (20),
hire_date DATE NOT NULL,
job_id VARCHAR (10) NOT NULL,
salary INT,
commission_pct INT,
manager_id INT,
department_id INT,
CONSTRAINT emp_email_uk1 UNIQUE (email),
CONSTRAINT emp_dept_fk1 FOREIGN KEY
(department_id)
REFERENCES dmhr.department (department_id),
CHECK (salary > 0)
)
PARTITION BY RANGE(hire_date)
(
PARTITION p1 VALUES LESS THAN (‘2007-1-1’),
PARTITION p2 VALUES LESS THAN (‘2008-1-1’),
PARTITION p3 VALUES LESS THAN (‘2009-1-1’),
PARTITION p4 VALUES LESS THAN (‘2010-1-1’),
PARTITION p5 VALUES LESS THAN (‘2011-1-1’),
PARTITION p6 VALUES LESS THAN (‘2012-1-1’),
PARTITION p7 VALUES LESS THAN (‘2013-1-1’),
PARTITION p8 VALUES LESS THAN (‘2014-1-1’),
PARTITION p9 VALUES LESS THAN (‘2015-1-1’),
PARTITION p10 VALUES LESS THAN (‘2016-1-1’),
PARTITION pmax VALUES LESS THAN (MAXVALUE))
STORAGE (NOBRANCH
);

插入数据并提交，示例语句如下：

INSERT INTO dmhr.rp_hiredt_emp SELECT * FROM dmhr.employee;
COMMIT;

判断一张表是否为分区表，如果 partitioned 字段为 yes ，该表为分区表。示例语句如下：

SELECT partitioned FROM dba_tables WHERE table_name=‘RP_HIREDT_EMP’;

查看表的分区状态，示例语句如下：

SELECT partitioning_type, partition_count, partitioning_key_count,
def_tablespace_name,status FROM dba_part_tables;

输出结果：

查看表的分区状态

查看所有的表分区，示例语句如下：

SELECT partition_name, high_value, tablespace_name FROM dba_tab_partitions
WHERE table_name=‘RP_HIREDT_EMP’;

输出结果：

查看表分区

列表分区

按照公司员工的职位 (job_id) 创建列表分区，示例语句如下：

CREATE TABLE dmhr.lp_job_emp
(
employee_id INT PRIMARY KEY,
employee_name VARCHAR (20),
identity_card VARCHAR (18),
email VARCHAR (50) NOT NULL,
phone_num VARCHAR (20),
hire_date DATE NOT NULL,
job_id VARCHAR (10) NOT NULL,
salary INT,
commission_pct INT,
manager_id INT,
department_id INT,
CONSTRAINT emp_email_uk2 UNIQUE (email),
CONSTRAINT emp_dept_fk2 FOREIGN KEY
(department_id)
REFERENCES dmhr.department (department_id),
CHECK (salary > 0)
)
PARTITION BY LIST (job_id)
(PARTITION p1 VALUES (‘11’,‘12’,‘21’,‘22’),
PARTITION p2 VALUES (‘31’,‘32’,‘41’,‘42’),
PARTITION p3 VALUES (‘51’,‘52’,‘61’,‘62’),
PARTITION p4 VALUES (‘71’,‘72’,‘81’,‘82’),
PARTITION pmax VALUES (DEFAULT))
STORAGE (NOBRANCH);

插入数据并提交，示例语句如下：

INSERT INTO dmhr.lp_job_emp SELECT * FROM dmhr.employee;
COMMIT;

查询各分区的行数和累加行数，示例语句如下：

WITH p AS
(SELECT ‘P1’ AS pars, COUNT() AS num FROM dmhr.lp_job_emp PARTITION (P1)
UNION ALL
SELECT ‘P2’, COUNT() FROM dmhr.lp_job_emp PARTITION (P2)
UNION ALL
SELECT ‘P3’, COUNT() FROM dmhr.lp_job_emp PARTITION (P3)
UNION ALL
SELECT ‘P4’, COUNT() FROM dmhr.lp_job_emp PARTITION (P4))
SELECT pars, num, SUM(num) OVER(order by pars rows unbounded preceding)
row_sum FROM p;

输出结果：

查询各分区的行数和累加行数

哈希分区

按照员工的 email 将员工信息数据 department 打散到 4 个分区中。示例语句如下：

CREATE TABLE dmhr.hp_email_emp
(
employee_id INT PRIMARY KEY,
employee_name VARCHAR (20),
identity_card VARCHAR (18),
email VARCHAR (50) NOT NULL,
phone_num VARCHAR (20),
hire_date DATE NOT NULL,
job_id VARCHAR (10) NOT NULL,
salary INT,
commission_pct INT,
manager_id INT,
department_id INT,
CONSTRAINT emp_email_uk3 UNIQUE (email),
CONSTRAINT emp_dept_fk3 FOREIGN KEY
(department_id)
REFERENCES dmhr.department (department_id),
CHECK (salary > 0)
)
PARTITION BY HASH (email)
(PARTITION p1, PARTITION p2, PARTITION p3, PARTITION p4)
STORAGE (NOBRANCH);

插入数据并提交，示例语句如下：

INSERT INTO dmhr.hp_email_emp SELECT * FROM dmhr.employee;
COMMIT;

查询各分区的行数和累加行数，示例语句如下：

WITH p AS
(SELECT ‘P1’ AS pars, COUNT() AS num FROM dmhr.hp_email_emp PARTITION (P1)
UNION ALL
SELECT ‘P2’, COUNT() FROM dmhr.hp_email_emp PARTITION (P2)
UNION ALL
SELECT ‘P3’, COUNT() FROM dmhr.hp_email_emp PARTITION (P3)
UNION ALL
SELECT ‘P4’, COUNT() FROM dmhr.hp_email_emp PARTITION (P4))
SELECT pars, num, SUM(num) OVER(order by pars rows unbounded preceding)
row_sum FROM p;

输出结果：

查询哈希分区行数

可以看出数据在哈希表的各个分区上分布比较均匀。

组合分区

组合分区是指范围分区、列表分区或哈希分区的两两组合，以下介绍其中一种组合分区 range-hash。

员工的雇佣时间为主分区键，从 2007 年起，每 3 年划分一个分区，每个分区包含 2 个 hash 子分区。示例语句如下：

CREATE TABLE dmhr.rhp_emp
(
employee_id INT PRIMARY KEY,
employee_name VARCHAR (20),
identity_card VARCHAR (18),
email VARCHAR (50) NOT NULL,
phone_num VARCHAR (20),
hire_date DATE NOT NULL,
job_id VARCHAR (10) NOT NULL,
salary INT,
commission_pct INT,
manager_id INT,
department_id INT,
CONSTRAINT emp_email_uk4 UNIQUE (email),
CONSTRAINT emp_dept_fk4 FOREIGN KEY
(department_id)
REFERENCES dmhr.department (department_id),
CHECK (salary > 0)
)
PARTITION BY RANGE (hire_date)
SUBPARTITION BY HASH (email)
SUBPARTITION TEMPLATE (SUBPARTITION p1 , SUBPARTITION p2 )
(
PARTITION p1 VALUES LESS THAN (‘2010-1-1’),
PARTITION p2 VALUES LESS THAN (‘2013-1-1’),
PARTITION p3 VALUES LESS THAN (‘2016-1-1’),
PARTITION pmax VALUES LESS THAN (MAXVALUE))
STORAGE (NOBRANCH);

查询每个主分区下的子分区，示例语句如下：

SELECT partition_name,
subpartition_count,
composite,
high_value,
tablespace_name
FROM dba_tab_partitions
WHERE table_name = UPPER (‘rhp_emp’)
ORDER BY partition_position;

输出结果：

查询组合分区

查询组合分区子分区的键信息，示例语句如下：

SELECT name,object_type,column_name,column_position FROM user_subpart_key_columns;

组合分区子分区的键信

通过系统表 user_subpart_key_columns 查询组合分区子分区的键信息，必须以 dmhr 用户登录库。

间隔分区

间隔分区可以在输入相应分区的数据后自动创建分区，是范围分区的扩展。例如：将 dmhr 用户下 employee 表中员工信息按入职时间以年为间隔转换为分区表。

创建分区表 emp_part，起始分区时间为小于 2007 年 1 月 1 日。示例语句如下：

CREATE TABLE dmhr.emp_part
(
EMPLOYEE_ID INT PRIMARY KEY,
EMPLOYEE_NAME VARCHAR (20),
IDENTITY_CARD VARCHAR (18),
EMAIL VARCHAR (50) NOT NULL,
PHONE_NUM VARCHAR (20),
HIRE_DATE DATE NOT NULL,
JOB_ID VARCHAR (10) NOT NULL,
SALARY INT,
COMMISSION_PCT INT,
MANAGER_ID INT,
DEPARTMENT_ID INT
)
PARTITION BY RANGE (hire_date)
INTERVAL ( NUMTOYMINTERVAL (1, ‘year’) )
(
PARTITION
P_BEFORE_2007
VALUES LESS THAN (TO_DATE (‘2007-01-01’, ‘yyyy-mm-dd’)))
STORAGE (FILLFACTOR 85, BRANCH(32,32));

在 emp_part 表中插入员工信息，示例语句如下：

INSERT INTO dmhr.emp_part SELECT * FROM dmhr.employee;
commit;

通过 dba_tab_partitions 表查询分区信息，示例语句如下：

SELECT table_name,partition_name, high_value FROM dba_tab_partitions
WHERE table_name=‘EMP_PART’ ORDER BY high_value;

输出结果：

查询分区信息

如上图所示，数据库以 2007 年 1 月 1 日为起始点自动创建了 9 个分区。

按分区名称检索数据如下，示例语句如下：

SELECT * FROM dmhr.emp_part PARTITION(P_BEFORE_2007);

插入数据，自动新增分区表。在 emp_part 表中插入一条新记录，员工入职时间为 2020 年 5 月 30 日。示例语句如下：

INSERT INTO dmhr.emp_part(EMPLOYEE_ID,EMPLOYEE_NAME,IDENTITY_CARD,EMAIL,
PHONE_NUM,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)
VALUES(9990,‘武达梦’,‘340102196202303999’,‘wudm@dameng.com’,‘15312348566’,
‘2020-05-30’,‘11’,50000.00,0,1001,101);

commit;

通过系统表 dba_tab_partitions 查询新增数据分区，示例语句如下：

SELECT table_name,partition_name, high_value FROM dba_tab_partitions
WHERE table_name=‘EMP_PART’ ORDER BY high_value;

输出结果：

新增数据分区

注意
如果是 sysdba 用户登录数据库，通过系统表 dba_tab_partitions 查询。
如果是 dmhr 用户登录数据库，通过系统表 user_tab_partitions 查询。

如上图所示，数据库自动创建了 1 个新分区存储的 high_value 为 DATE’2021-01-01’，总分区数从 9 增加至 10。

分区表的维护
增加分区
ALTER TABLE ADD PARTITION 语句将新分区增加到最后一个现存分区的后面。以上面建立的范围分区 rp_hiredt_emp为例。

增加一个分区存储 2016 年雇佣的员工信息，示例语句如下：

ALTER TABLE dmhr.rp_hiredt_emp ADD PARTITION p_before_2017
VALUES LESS THAN (‘2017-1-1’);

输出结果：

新增分区出错

【问题原因】：

分区表中已含有 MAXVALUE 分区，无法再增加分区。

【解决方法】：

使用 SPLIT PARTITION 子句对分区进行拆分。删除 MAXVALUE 分区，新增分区后，再加上 MAXVALUE 分区。示例语句如下：

//1. 删除 MAXVALUE 分区
ALTER TABLE dmhr.rp_hiredt_emp DROP PARTITION pmax;

//2. 新增分区
ALTER TABLE dmhr.rp_hiredt_emp ADD PARTITION p_before_2017 VALUES
LESS THAN (‘2017-1-1’);

//3. 增加 MAXVALUE 分区
ALTER TABLE dmhr.rp_hiredt_emp ADD PARTITION pmax VALUES LESS THAN (MAXVALUE);

删除分区

使用 DROP PARTITION 可将分区的定义连同数据一起删除，示例语句如下：
ALTER TABLE dmhr.rp_hiredt_emp DROP PARTITION pmax;

使用 TRUNCATE PARTITION 仅删除分区的数据，示例语句如下：
ALTER TABLE dmhr.rp_hiredt_emp TRUNCATE PARTITION p7;

合并分区

将 2008 年和 2009 年入职员工各的分区合并为一个分区。

查询分区信息，示例语句如下：
SELECT ‘P3’ AS pars, COUNT() AS num FROM dmhr.rp_hiredt_emp PARTITION (P3)
UNION ALL
SELECT ‘P4’, COUNT() FROM dmhr.rp_hiredt_emp PARTITION (P4)

输出结果：

合并分区前

使用 MERGE……INTO 合并两个分区，示例语句如下：
ALTER TABLE dmhr.rp_hiredt_emp MERGE PARTITIONS p3, p4 into partition p3_4;

查询合并后的分区的记录数，示例语句如下：
SELECT COUNT(*) AS num FROM dmhr.rp_hiredt_emp PARTITION (P3_4);

输出结果：

合并分区后

注意
仅范围分区支持合并，并且待合并的分区必须相邻。

拆分分区

当一个分区变得太大以至于要用很长时间才能完成备份、恢复或维护操作时，可考虑将分区进行拆分。

将上例中已合并的分区 P3_4 重新拆分成 P3 和 P4.

使用 SPLIT……INTO 拆分分区，示例语句如下：
ALTER TABLE dmhr.rp_hiredt_emp SPLIT PARTITION p3_4 AT (‘2009-01-01’)
INTO (PARTITION p3, PARTITION p4);

查询拆分得到的 2 个分区，示例语句如下：
SELECT ‘P3’ AS pars, COUNT() AS num FROM rp_hiredt_emp PARTITION (P3)
UNION ALL
SELECT ‘P4’, COUNT() FROM rp_hiredt_emp PARTITION (P4)

输出结果：

拆分后分区

注意
拆分分区会导致数据的重组和分区索引的重建。因此，拆分分区可能比较耗时，所需时间取决于分区数据量的大小。

分区索引

支持对水平分区表建立普通索引、唯一索引、聚集索引和函数索引。
创建索引时若未指定 GLOBAL 关键字则建立的索引是局部索引。
局部索引是指每个表分区都对应一个索引分区，并且只能检索该分区上的数据。
全局索引是指每个表分区的数据都被索引在同一个 B 树中。
目前，仅堆表的水平分区表支持 GLOBAL全局索引，堆表上的 primary key 会自动变为全局索引。
在 lp_job_emp 表上的 salary 列上建立普通局部索引，示例语句如下：

CREATE INDEX ind_sal ON dmhr.lp_job_emp(salary);

在 lp_job_emp 表上的 email 列上建立局部唯一索引，同时必须将分区键 job_id 列入，示例语句如下：

CREATE UNIQUE INDEX ind_mail ON dmhr.lp_job_emp(job_id, email);

建立局部分区索引后，每一个分区子表都会建立一个索引分区，负责索引分区子表的数据。每个索引分区只负责索引本分区上的数据，其他分区上的数据无法维护。

查询分区索引如下，示例语句如下：

SELECT * FROM user_ind_partitions WHERE index_name=UPPER(‘ind_sal’)
ORDER BY partition_name;

查询分区索引

通过 user_ind_partitions 系统表查询分区索引信息，必须以 dmhr 用户登录数据库。

SELECT index_name, index_type, table_name, uniqueness, tablespace_name, status,
partitioned FROM dba_indexes WHERE TABLE_NAME=UPPER(‘lp_job_emp’);

输出结果：

查询分区索引

注意
不能在水平分区表上建立局部唯一函数索引，只能建立分区键索引，即分区键必须都包含在索引键中。

非分区表转换成分区表

根据原表结构创建所需的分区表，建立各种约束，转换方法如下：

使用 INSERT TABLE <分区表名> SELECT * FROM <非分区表>。
使用 dexp/dimp 将数据从源表导出，再导入到目标分区表。
下面演示第二种情况，目标表为 dmhr 中的 department 表，department_id 为表的主键列。按 department_id 建立范围分区，并且每个分区对应不同的表空间。

数据准备，示例语句如下：
CREATE TABLE dmhr.dept AS SELECT * FROM dmhr.department;
ALTER TABLE dmhr.dept ADD PRIMARY KEY (department_id);

使用 dexp 工具将源表数据导出，示例语句如下：
dexp dmhr/dmhr20201111@172.16.100.80:5237 FILE=dept.dmp LOG=dexp_dept.log
DIRECTORY=/dm/backup/ TABLES=dmhr.dept

使用 dimp 工具将数据导入目标分区表。由于 DM 数据库目前不支持导入导出表映射，故先 drop 掉源表，然后建立同名的分区表。示例语句如下：
DROP TABLE dmhr.dept;

CREATE TABLE dmhr.dept
(
department_id NUMBER PRIMARY KEY,
department_name VARCHAR (30) NOT NULL,
manager_id INT,
location_id INT
)
PARTITION BY RANGE
(department_id)
(
PARTITION p1 VALUES LESS THAN (400) TABLESPACE tbs_dept_400,
PARTITION p2 VALUES LESS THAN (700) TABLESPACE tbs_dept_700,
PARTITION p3 VALUES LESS THAN (1000) TABLESPACE tbs_dept_1000,
PARTITION pmax VALUES LESS THAN (MAXVALUE) TABLESPACE tbs_dept_1000
）
）

数据导出，示例语句如下：
dimp dmhr/dmhr20201111@172.16.100.80:5237 FILE=dept.dmp LOG=dimp_dept.log
DIRECTORY=/dm/backup/ TABLES=dmhr.dept TABLE_EXISTS_ACTION=truncate IGNORE=Y

查看分区信息，示例语句如下：
SELECT table_name, owner, partitioned FROM dba_tables WHERE table_name=UPPER(‘dept’);

输出结果：

查询分区信息1

SELECT ‘P1’ AS pars, COUNT() AS num FROM dmhr.dept PARTITION (P1)
UNION ALL
SELECT ‘P2’, COUNT() FROM dmhr.dept PARTITION (P2)
UNION ALL
SELECT ‘P3’, COUNT(*) FROM dmhr.dept PARTITION (P3)
UNION ALL

SELECT ‘PMAX’, COUNT(*) FROM dmhr.dept PARTITION (PMAX)

输出结果：

查询分区信息2

二级分区

LIST-RANG
CREATE TABLE SALES(
SALES_ID INT,
SALEMAN CHAR(20),
SALEDATE DATETIME,
CITY CHAR(10)
)
PARTITION BY LIST(CITY)
SUBPARTITION BY RANGE(SALEDATE) SUBPARTITION TEMPLATE(
SUBPARTITION P11 VALUES LESS THAN (‘2012-04-01’),
SUBPARTITION P12 VALUES LESS THAN (‘2012-07-01’),
SUBPARTITION P13 VALUES LESS THAN (‘2012-10-01’),
SUBPARTITION P14 VALUES EQU OR LESS THAN (MAXVALUE))
(
PARTITION P1 VALUES (‘北京’, ‘天津’)
(
SUBPARTITION P11_1 VALUES LESS THAN (‘2012-10-01’),
SUBPARTITION P11_2 VALUES EQU OR LESS THAN (MAXVALUE)
),
PARTITION P2 VALUES (‘上海’, ‘南京’, ‘杭州’),
PARTITION P3 VALUES (DEFAULT)
);

三级分区

DM 支持最多八层多级分区
CREATE TABLE STUDENT(NAME VARCHAR(20), AGE INT, SEX VARCHAR(10) CHECK (SEX
IN (‘MAIL’,‘FEMAIL’)), GRADE INT CHECK (GRADE IN (7,8,9)))
PARTITION BY LIST(GRADE)
SUBPARTITION BY LIST(SEX) SUBPARTITION TEMPLATE
(
SUBPARTITION Q1 VALUES(‘MAIL’),
SUBPARTITION Q2 VALUES(‘FEMAIL’)
),
SUBPARTITION BY RANGE(AGE) SUBPARTITION TEMPLATE
(
SUBPARTITION R1 VALUES LESS THAN (12),
SUBPARTITION R2 VALUES LESS THAN (15),
SUBPARTITION R3 VALUES LESS THAN (MAXVALUE)
)
(
PARTITION P1 VALUES (7),
PARTITION P2 VALUES (8),
PARTITION P3 VALUES (9)
);

间隔分区表范围分区的扩展

----按年创建间隔分区
CREATE TABLE interval_y
(
employee_id NUMBER,
employee_name VARCHAR2(20),
birthday DATE
)
PARTITION BY RANGE(birthday)
INTERVAL (NUMTOYMINTERVAL(1,‘year’))
(
PARTITION partition2014 VALUES LESS THAN(‘2015-01-
01’),
PARTITION partition2015 VALUES LESS THAN(‘2016-01-
01’)
);

----按月创建间隔分区
CREATE TABLE interval_m
(
employee_id NUMBER,
employee_name VARCHAR2(20),
birthday DATE
)
PARTITION BY RANGE(birthday)
INTERVAL (NUMTOYMINTERVAL(1,‘month’))
(
PARTITION partition201401 VALUES LESS THAN(‘2014-02-
01’),
PARTITION partition201402 VALUES LESS THAN(‘2014-03-
01’)
);
达梦不支持NUMTODSINTERVAL按日生成间隔分区，报错如下
CREATE TABLE i1
(
employee_id NUMBER,
employee_name VARCHAR2(20),
birthday DATE
)
PARTITION BY RANGE(birthday)
INTERVAL (NUMTODSINTERVAL(1,‘day’))
(
PARTITION partition20140101 VALUES LESS THAN(‘2014-01-
02’)
);

Error in line[-2894]: 12
Interval expression not match type of part col

分区置换

非区分表转换为分区表的方法有多种，可以使用逻辑导出导入的方式转化，也可以借用insert into … select的方式，本章介绍使用分区交换将非分区表转换为分区表。

1.构建测试表和测试数据

create table no_part(
id int,
name varchar(20)
) tablespace main;

插入数据
begin
for i in 1…1000 loop
insert into no_part(id, name)
values(i, DBMS_RANDOM.RANDOM_STRING(‘U’,5)||’:’|| lpad(i,4, ‘0’));
end loop;
end;

select count(*)from no_part;

2.创建分区表

create table yes_part(
id int,
name varchar(20)
) partition by range(id) (
partition p1 values less than (200),
partition p2 values less than (400),
partition p3 values less than (600),
partition p4 values less than (800),
partition pN values less than (maxvalue));

3.使用exchange交换分区

alter table yes_part exchange partition pN with table no_part;

select count()from no_part;
–0
select count()from yes_part;
–1000

数据分布
select ‘p1’ partition_name, count() num from yes_part partition(p1)
union all
select ‘p2’, count() from yes_part partition(p2)
union all
select ‘p3’, count() from yes_part partition(p3)
union all
select ‘p4’, count() from yes_part partition(p4)
union all
select ‘pN’, count(*) from yes_part partition(pN);

可以看出普通非分区表的数据交换到分区表的某个分区后，数据并不进行校验，也就是说不管是否满足该分区的范围，数据都会进入该分区。

4.使用split将分区拆分

上述数据交换后，所有数据都在一个分区，完全不符合我们将数据放入分区表的初衷，此时可以使用分区拆分，分区拆分会对分区中的数据进行重组，
拆分后数据会重新按照分区范围分布。执行以下命令进行分区拆分（仅范围分区支持分区拆分）：

alter table yes_part split partition pN at (1000) into (partition p5, partition pmax);

select ‘p1’ partition_name, count() num from yes_part partition(p1)
union all
select ‘p2’, count() from yes_part partition(p2)
union all
select ‘p3’, count() from yes_part partition(p3)
union all
select ‘p4’, count() from yes_part partition(p4)
union all
select ‘p5’, count() from yes_part partition(p5)
union all
select ‘pmax’, count() from yes_part partition(pmax);

5.merge合并分区

上述测试中，如果觉得pmax分区单独存在浪费，数据可以与分区p5合并，可以使用分区合并merge将p5和pmax合并，合并后的分区可以使用原来的名称，也可以使用新的名称，参考如下语句：

alter table yes_part merge partitions p5, pmax into partition pN;

使用分区交换exchange需注意：

分区交换仅支持范围和列表分区，不支持HASH分区。
分区交换可以将非分区表的数据交换到分区表，也可以将分区表的数据交换到非分区表，本质上是源表和目标表数据的互换。
分区交换要求分区表与交换表具有相同的结构（相同的表类型、相同的BRANCH选项、相同的列结构、相同的索引、相同的分布方式），否则会报“[-7000]:交换对象不匹配”的错误。
不支持含全局索引的分区表与普通表进行交换分区操作。
分区交换时不会进行数据校验，如果交换表的数据不符合分区范围，数据仍然会进入该分区。此时如果是范围分区可以使用split拆分分区，系统会自动对数据进行重组。
在生产环境中，为保证数据安全，建议对源表的数据备份后再做分区交换。

yangeoooo

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
达梦分区表

二级分区LIST-RANGCREATE TABLE SALES(SALES_ID INT,SALEMAN CHAR(20),SALEDATE DATETIME,CITY CHAR(10))PARTITION BY LIST(CITY)SUBPARTITION BY RANGE(SALEDATE) SUBPARTITION TEMPLATE(SUBPARTITION P11 VALUES LESS THAN (‘2012-04-01’),SUBPARTITION P12 VALUE
复制链接

扫一扫