梁敬彬梁敬弘兄弟出品

在数据库设计中，分区表和唯一索引都是常用的数据库对象。分区表可以提高大表的管理和查询效率，而唯一索引则用于确保数据的唯一性。然而，在达梦数据库中使用分区表时，会遇到一个特殊的限制：局部唯一索引必须包含全部分区列。本文将详细分析这一限制及其解决方案。

1. 问题复现

首先，让我们通过实例来观察这一限制。

1.1 创建普通分区表

SQL> create table range_part_tab (id number,deal_date date,area_code number,nbr number,contents varchar2(4000))
     partition by range (deal_date)
     (
     partition p_202401 values less than (TO_DATE('2024-02-01', 'YYYY-MM-DD')),
     partition p_202402 values less than (TO_DATE('2024-03-01', 'YYYY-MM-DD')),
     partition p_202403 values less than (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
     partition p_202404 values less than (TO_DATE('2024-05-01', 'YYYY-MM-DD')),
     partition p_202405 values less than (TO_DATE('2024-06-01', 'YYYY-MM-DD')),
     partition p_202406 values less than (TO_DATE('2024-07-01', 'YYYY-MM-DD')),
     partition p_202407 values less than (TO_DATE('2024-08-01', 'YYYY-MM-DD')),
     partition p_202408 values less than (TO_DATE('2024-09-01', 'YYYY-MM-DD')),
     partition p_202409 values less than (TO_DATE('2024-10-01', 'YYYY-MM-DD')),
     partition p_202410 values less than (TO_DATE('2024-11-01', 'YYYY-MM-DD')),
     partition p_202411 values less than (TO_DATE('2024-12-01', 'YYYY-MM-DD')),
     partition p_202412 values less than (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
     partition p_202501 values less than (TO_DATE('2025-02-01', 'YYYY-MM-DD')),
     partition p_202502 values less than (TO_DATE('2025-03-01', 'YYYY-MM-DD')),
     partition p_max values less than (maxvalue)
     );

操作已执行

已用时间: 102.731(毫秒). 执行号:12.

1.2 尝试添加主键约束

SQL> alter table range_part_tab add constraint range_tab_pk primary key (id);

alter table range_part_tab add constraint range_tab_pk primary key (id);

第1 行附近出现错误[-2683]:局部唯一索引必须包含全部分区列.

已用时间: 0.897(毫秒). 执行号:0.

1.3 尝试在创建表时直接设置主键

SQL> drop table range_part_tab;

操作已执行

已用时间: 15.532(毫秒). 执行号:1.

SQL> create table range_part_tab (id number primary key, deal_date date, area_code number, nbr number, contents varchar2(4000))
     partition by range (deal_date)
     (
     partition p_202401 values less than (TO_DATE('2024-02-01', 'YYYY-MM-DD')),
     partition p_202402 values less than (TO_DATE('2024-03-01', 'YYYY-MM-DD')),
     partition p_202403 values less than (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
     partition p_202404 values less than (TO_DATE('2024-05-01', 'YYYY-MM-DD')),
     partition p_202405 values less than (TO_DATE('2024-06-01', 'YYYY-MM-DD')),
     partition p_202406 values less than (TO_DATE('2024-07-01', 'YYYY-MM-DD')),
     partition p_202407 values less than (TO_DATE('2024-08-01', 'YYYY-MM-DD')),
     partition p_202408 values less than (TO_DATE('2024-09-01', 'YYYY-MM-DD')),
     partition p_202409 values less than (TO_DATE('2024-10-01', 'YYYY-MM-DD')),
     partition p_202410 values less than (TO_DATE('2024-11-01', 'YYYY-MM-DD')),
     partition p_202411 values less than (TO_DATE('2024-12-01', 'YYYY-MM-DD')),
     partition p_202412 values less than (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
     partition p_202501 values less than (TO_DATE('2025-02-01', 'YYYY-MM-DD')),
     partition p_202502 values less than (TO_DATE('2025-03-01', 'YYYY-MM-DD')),
     partition p_max values less than (maxvalue)
     );

第20 行附近出现错误[-2683]:局部唯一索引必须包含全部分区列.

已用时间: 0.527(毫秒). 执行号:0.

2. 限制分析

这一限制的根本原因在于达梦数据库分区表的实现机制。达梦数据库对分区表的局部唯一索引有以下要求：

局部唯一性限制：在分区表中，局部唯一索引只能保证分区内的唯一性，而非整表唯一性
分区键包含要求：局部唯一索引必须包含全部分区列，这样才能确保在特定分区内维护唯一性
主键约束影响：由于主键约束会自动创建唯一索引，因此主键也受此限制影响
这一设计与Oracle数据库相似，目的是为了确保分区表的索引结构与分区策略保持一致，便于数据库引擎更高效地管理索引数据。

3. 解决方案

针对这一限制，达梦数据库提供了几种有效的解决方案，下面详细介绍各方案的实现方式与适用场景。

3.1 方案一：包含分区列的复合主键

最直接的解决方案是将分区列包含在主键中，创建复合主键：

SQL> drop table range_part_tab;

操作已执行

已用时间: 12.283(毫秒). 执行号:2.

SQL> create table range_part_tab (id number, deal_date date, area_code number, nbr number, contents varchar2(4000))
     partition by range (deal_date)
     (
     partition p_202401 values less than (TO_DATE('2024-02-01', 'YYYY-MM-DD')),
     partition p_202402 values less than (TO_DATE('2024-03-01', 'YYYY-MM-DD')),
     partition p_202403 values less than (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
     partition p_202404 values less than (TO_DATE('2024-05-01', 'YYYY-MM-DD')),
     partition p_202405 values less than (TO_DATE('2024-06-01', 'YYYY-MM-DD')),
     partition p_202406 values less than (TO_DATE('2024-07-01', 'YYYY-MM-DD')),
     partition p_202407 values less than (TO_DATE('2024-08-01', 'YYYY-MM-DD')),
     partition p_202408 values less than (TO_DATE('2024-09-01', 'YYYY-MM-DD')),
     partition p_202409 values less than (TO_DATE('2024-10-01', 'YYYY-MM-DD')),
     partition p_202410 values less than (TO_DATE('2024-11-01', 'YYYY-MM-DD')),
     partition p_202411 values less than (TO_DATE('2024-12-01', 'YYYY-MM-DD')),
     partition p_202412 values less than (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
     partition p_202501 values less than (TO_DATE('2025-02-01', 'YYYY-MM-DD')),
     partition p_202502 values less than (TO_DATE('2025-03-01', 'YYYY-MM-DD')),
     partition p_max values less than (maxvalue)
     );

操作已执行

已用时间: 97.432(毫秒). 执行号:3.

SQL> alter table range_part_tab add constraint range_tab_pk primary key (id, deal_date);

操作已执行

已用时间: 188.197(毫秒). 执行号:4.

优点：

完全兼容分区表的局部唯一索引要求
保持了表的分区特性和优势
无需改变表的存储结构
缺点：

主键包含了非业务必需的字段（如果id已经能唯一标识记录）
可能影响引用该主键的外键设计
应用程序可能需要修改，以适应新的主键结构

3.2 方案二：使用堆表存储结构

另一种解决方案是将表的存储结构设置为堆表（NOBRANCH）：

SQL> drop table range_part_tab;

操作已执行

已用时间: 11.857(毫秒). 执行号:5.

SQL> create table range_part_tab (id number primary key, deal_date date, area_code number, nbr number, contents varchar2(4000))
     partition by range (deal_date)
     (
     partition p_202401 values less than (TO_DATE('2024-02-01', 'YYYY-MM-DD')),
     partition p_202402 values less than (TO_DATE('2024-03-01', 'YYYY-MM-DD')),
     partition p_202403 values less than (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
     partition p_202404 values less than (TO_DATE('2024-05-01', 'YYYY-MM-DD')),
     partition p_202405 values less than (TO_DATE('2024-06-01', 'YYYY-MM-DD')),
     partition p_202406 values less than (TO_DATE('2024-07-01', 'YYYY-MM-DD')),
     partition p_202407 values less than (TO_DATE('2024-08-01', 'YYYY-MM-DD')),
     partition p_202408 values less than (TO_DATE('2024-09-01', 'YYYY-MM-DD')),
     partition p_202409 values less than (TO_DATE('2024-10-01', 'YYYY-MM-DD')),
     partition p_202410 values less than (TO_DATE('2024-11-01', 'YYYY-MM-DD')),
     partition p_202411 values less than (TO_DATE('2024-12-01', 'YYYY-MM-DD')),
     partition p_202412 values less than (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
     partition p_202501 values less than (TO_DATE('2025-02-01', 'YYYY-MM-DD')),
     partition p_202502 values less than (TO_DATE('2025-03-01', 'YYYY-MM-DD')),
     partition p_max values less than (maxvalue)
     )
     STORAGE (NOBRANCH);

操作已执行

已用时间: 24.119(毫秒). 执行号:6.
优点：

允许在不包含分区列的情况下创建主键
保持了表的分区特性
保持业务主键的设计不变

缺点：

更改了表的存储结构，可能影响某些查询性能
堆表的特性可能与原有的索引组织表有所不同
可能影响特定场景下的数据访问模式

3.3 方案三：全局索引方案

在达梦数据库较新版本（DM8及以上）中，可以使用全局索引方案：

SQL> drop table range_part_tab;

操作已执行

已用时间: 11.246(毫秒). 执行号:7.

SQL> create table range_part_tab (id number, deal_date date, area_code number, nbr number, contents varchar2(4000))
     partition by range (deal_date)
     (
     partition p_202401 values less than (TO_DATE('2024-02-01', 'YYYY-MM-DD')),
     partition p_202402 values less than (TO_DATE('2024-03-01', 'YYYY-MM-DD')),
     partition p_202403 values less than (TO_DATE('2024-04-01', 'YYYY-MM-DD')),
     partition p_202404 values less than (TO_DATE('2024-05-01', 'YYYY-MM-DD')),
     partition p_202405 values less than (TO_DATE('2024-06-01', 'YYYY-MM-DD')),
     partition p_202406 values less than (TO_DATE('2024-07-01', 'YYYY-MM-DD')),
     partition p_202407 values less than (TO_DATE('2024-08-01', 'YYYY-MM-DD')),
     partition p_202408 values less than (TO_DATE('2024-09-01', 'YYYY-MM-DD')),
     partition p_202409 values less than (TO_DATE('2024-10-01', 'YYYY-MM-DD')),
     partition p_202410 values less than (TO_DATE('2024-11-01', 'YYYY-MM-DD')),
     partition p_202411 values less than (TO_DATE('2024-12-01', 'YYYY-MM-DD')),
     partition p_202412 values less than (TO_DATE('2025-01-01', 'YYYY-MM-DD')),
     partition p_202501 values less than (TO_DATE('2025-02-01', 'YYYY-MM-DD')),
     partition p_202502 values less than (TO_DATE('2025-03-01', 'YYYY-MM-DD')),
     partition p_max values less than (maxvalue)
     );

操作已执行

已用时间: 96.543(毫秒). 执行号:8.

SQL> create unique index range_tab_pk_idx on range_part_tab(id) global;

操作已执行

已用时间: 45.782(毫秒). 执行号:9.

SQL> alter table range_part_tab add constraint range_tab_pk primary key (id) using index range_tab_pk_idx;

操作已执行

已用时间: 18.325(毫秒). 执行号:10.
优点：
  • 保持单字段主键的业务语义
  • 不需要修改应用程序逻辑
  • 适合需要整表唯一性约束的场景
缺点：
  • 分区维护操作（如分区交换、合并）可能性能降低
  • 全局索引的维护成本较高
  • 在大规模数据操作时可能成为性能瓶颈

4. 各方案的性能对比

不同解决方案在各种操作下的性能对比：
在这里插入图片描述

5. 实际应用示例

让我们通过具体示例来展示各解决方案的使用场景：

5.1 业务场景示例：订单系统

假设有一个电商订单系统，需要存储大量订单数据，并按订单日期分区：

SQL> create table orders (
     order_id number,
     order_date date,
     customer_id number,
     amount number(10,2),
     status varchar2(20)
     )
     partition by range (order_date)
     (
     partition p_202401 values less than (TO_DATE('2024-02-01', 'YYYY-MM-DD')),
     partition p_202402 values less than (TO_DATE('2024-03-01', 'YYYY-MM-DD')),
     /* 其他月份分区 */
     partition p_max values less than (maxvalue)
     );
5.1.1 复合主键方案（适合报表查询系统）
SQL> alter table orders add constraint orders_pk primary key (order_id, order_date);

-- 按订单ID和日期范围查询（高效）
SQL> select * from orders 
     where order_id = 12345 
     and order_date between to_date('2024-03-01','YYYY-MM-DD') 
     and to_date('2024-03-31','YYYY-MM-DD');
5.1.2 堆表结构方案（适合高并发OLTP系统）
SQL> create table orders (
     order_id number primary key,
     order_date date,
     customer_id number,
     amount number(10,2),
     status varchar2(20)
     )
     partition by range (order_date)
     (
     partition p_202401 values less than (TO_DATE('2024-02-01', 'YYYY-MM-DD')),
     /* 其他分区 */
     )
     STORAGE (NOBRANCH);
     
-- 按订单ID直接查询（高效）
SQL> select * from orders where order_id = 12345;
5.1.3 全局索引方案（适合混合OLTP/OLAP系统）
SQL> create table orders (
     order_id number,
     order_date date,
     customer_id number,
     amount number(10,2),
     status varchar2(20)
     )
     partition by range (order_date)
     (
     partition p_202401 values less than (TO_DATE('2024-02-01', 'YYYY-MM-DD')),
     /* 其他分区 */
     );
     
SQL> create unique index orders_pk_idx on orders(order_id) global;
SQL> alter table orders add constraint orders_pk primary key (order_id) using index orders_pk_idx;

-- 按日期查询特定分区数据（高效）
SQL> select * from orders 
     where order_date between to_date('2024-03-01','YYYY-MM-DD') 
     and to_date('2024-03-31','YYYY-MM-DD');
     
-- 按订单ID查询（通过全局索引，也很高效）
SQL> select * from orders where order_id = 12345;

总结

达梦数据库在分区表上的局部唯一索引必须包含全部分区列的限制，是由其内部实现机制决定的。本文介绍的三种解决方案各有优缺点：

  1. 复合主键方案：最简单直接，适合查询同时使用主键和分区列的场景
  2. 堆表结构方案：保持单字段主键，适合高并发OLTP系统
  3. 全局索引方案：灵活性最高，但在分区维护操作时性能开销较大

选择哪种方案，应根据具体的业务需求、查询模式和性能要求进行评估。在设计数据库时，提前考虑分区策略与主键设计的协调性，可以避免后期的调整成本。

达梦数据库知识总结链接贴

国内一线知名数据库专家、畅销书《收获，不止Oracle》作者梁敬彬老师，做客【达梦会客厅】

公众号：收获不止数据库

系列回顾
“大白话人工智能” 系列
“数据库拍案惊奇” 系列
“世事洞明皆学问” 系列

# 数据库 # oracle # sql
Logo
EazyDevelop社区

一站式 AI 云服务平台

更多推荐

cover

5分钟搞定！MySQL/PostgreSQL 到 Elasticsearch 的实时同步

avatar EazyDevelop社区
cover

国内低代码平台：2025 年国内主流平台盘点

avatar EazyDevelop社区
cover

两小时，我搭了一套销售提成计算系统

avatar EazyDevelop社区