暂无图片
暂无图片
1
暂无图片
暂无图片
暂无图片
首页 / 达梦数据库的分区表

达梦数据库的分区表

原创 dbtiger 2022-08-09
1935

1.分区表的作用

  1. 提高可用性，一个表空间损坏不影响其他表空间；
  2. 利于单元化管理(备份和恢复)，大大减少恢复时间；
  3. 提高IO操作能力，表数据均衡打散存放在不同磁盘上。

2.分区表创建前表空间的规划

注：
1.达梦默认类型的表是索引组织表，在创建分区表的时候，主键列必须在分区范围内。
2.达梦的堆表上创建分区时，各个分区需要放在相同的表空间上。

创建表空间

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;

3.创建分区表

如果表上存在聚集索引且索引键为主键，并希望各个分区放置在不同表空间上，优化 IO ，则必须在主键列中加入分区键，参考如下建表语句：
范围分区

CREATE TABLE SYSDBA.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)
)
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);

范围分区

指定列的值所在范围来划分。
以员工的入职时间为分区键，创建以年为间隔的范围分区。

CREATE TABLE SYSDBA.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)
)
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
);
查看是否为分区表
SQL> SELECT partitioned FROM dba_tables WHERE table_name='RP_HIREDT_EMP';
LINEID     PARTITIONED
---------- -----------
1          YES
used time: 7.485(ms). Execute id is 55800.
查看分区状态
SQL> SELECT partitioning_type, partition_count, partitioning_key_count,
def_tablespace_name,status FROM dba_part_tables;

image.png

查看表的所有分区
SELECT partition_name, high_value, tablespace_name FROM dba_tab_partitions
WHERE table_name='RP_HIREDT_EMP';

image.png

列表分区

按职位创建列表分区
CREATE TABLE SYSDBA.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)
)
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);
插入1行数据
INSERT INTO sysdba.lp_job_emp (employee_id,employee_name,email,hire_date,job_id)values(22,'test','zl2@dameng.com','2022-08-09','22');
COMMIT;
查询各分区的行数和累加行数
WITH p AS
(SELECT 'P1' AS pars, COUNT(*) AS num FROM sysdba.lp_job_emp PARTITION (P1)
UNION ALL
SELECT 'P2', COUNT(*) FROM sysdba.lp_job_emp PARTITION (P2)
UNION ALL
SELECT 'P3', COUNT(*) FROM sysdba.lp_job_emp PARTITION (P3)
UNION ALL
SELECT 'P4', COUNT(*) FROM sysdba.lp_job_emp PARTITION (P4))
SELECT pars, num, SUM(num) OVER(order by pars rows unbounded preceding)
row_sum FROM p;

image.png

哈希分区

按照员工的 email 将员工信息数据 department 打散到 4 个分区中

CREATE TABLE sysdba.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)
)
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;
查看hash分区数据均匀分布
WITH p AS
(SELECT 'P1' AS pars, COUNT(*) AS num FROM sysdba.hp_email_emp PARTITION (P1)
UNION ALL
SELECT 'P2', COUNT(*) FROM sysdba.hp_email_emp PARTITION (P2)
UNION ALL
SELECT 'P3', COUNT(*) FROM sysdba.hp_email_emp PARTITION (P3)
UNION ALL
SELECT 'P4', COUNT(*) FROM sysdba.hp_email_emp PARTITION (P4))
SELECT pars, num, SUM(num) OVER(order by pars rows unbounded preceding)
row_sum FROM p;

image.png

组合分区

组合分区指的是范围分区、列表分区或hash分区的两两组合

如下是rang-hash分区
CREATE TABLE SYSDBA.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)
)
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;

image.png

查询组合分区子分区的键信息
SELECT name,object_type,column_name,column_position FROM user_subpart_key_columns;

image.png

间隔分区

间隔分区是范围分区的扩展，在输入相应分区的数据后自动创建分区。

起始时间为2007-01-01的分区表：

CREATE TABLE SYSDBA.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));
插入数据
INSERT INTO sysdba.emp_part SELECT * FROM "SYSDBA"."RP_HIREDT_EMP";
commit;
查询分区信息

如下：自动创建了9个分区

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

image.png

按分区检索数据
SELECT * FROM SYSDBA.emp_part PARTITION(P_BEFORE_2007);

image.png

根据插入的数据自动新增分区表
INSERT INTO sysdba.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',
'2022-08-09','11',50000.00,0,1001,101);
commit;
再次查询分区信息
SELECT table_name,partition_name, high_value FROM dba_tab_partitions
WHERE table_name='EMP_PART' ORDER BY high_value;

image.png

4.分区表的维护

增加和删除分区

注：分区表在已含有 MAXVALUE 分区，无法再增加分区
使用 SPLIT PARTITION 子句对分区进行拆分。删除 MAXVALUE 分区，新增分区后，再加上 MAXVALUE 分区（如：增加一个分区存储 2016 年雇佣的员工信息）：

1.删除最大分区
ALTER TABLE sysdba.rp_hiredt_emp DROP PARTITION pmax;

image.png

2.新增分区
ALTER TABLE sysdba.rp_hiredt_emp ADD PARTITION p_before_2017 VALUES
LESS THAN ('2017-1-1');

image.png

3.增加最大值分区
ALTER TABLE sysdba.rp_hiredt_emp ADD PARTITION pmax VALUES LESS THAN (MAXVALUE);

image.png

合并分区

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

使用merge…into语句

ALTER TABLE sysdba.rp_hiredt_emp MERGE PARTITIONS p3, p4 into partition p3_4;

按新合并的分区查询

SELECT COUNT(*) AS num FROM  sysdba.rp_hiredt_emp PARTITION (P3_4);

image.png

拆分分区

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

SPLIT……INTO语句拆分分区

ALTER TABLE sysdba.rp_hiredt_emp SPLIT PARTITION p3_4 AT ('2009-01-01')
INTO (PARTITION p3, PARTITION p4);

image.png

分区索引

1.创建普通局部索引
CREATE INDEX ind_sal ON sysdba.lp_job_emp(salary);

image.png

2.局部唯一索引

同时必须将分区键 job_id 列入
注：建立局部分区索引后，每一个分区子表都会建立一个索引分区，负责索引分区子表的数据。每个索引分区只负责索引本分区上的数据，其他分区上的数据无法维护。

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

image.png

3.查询分区索引信息
SELECT index_name, index_type, table_name, uniqueness, tablespace_name, status,
partitioned FROM dba_indexes WHERE TABLE_NAME=UPPER('lp_job_emp');

非分区表转换成分区表

先备份数据（dexp导出或ctas另存为表的方式），然后drop原表，然后按条件创建符合应用场景的分区表，最后导入数据即可。

参考链接：https://eco.dameng.com/document/dm/zh-cn/start/
image.png

墨力计划国产数据库达梦数据库
最后修改时间：2022-08-09 17:57:06
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

文章被以下合辑收录

达梦数据库（共8篇）
达梦数据库

评论