3. 范围分区实战

范围分区（Range Partitioning）是 Oracle 分区表中最常用、也最贴近“大规模数据治理”场景的一种分区方式。它的核心思想是：为分区键的取值划分若干左闭右开的连续区间，每一行数据根据分区键落入唯一一个区间，从而被路由到对应分区。时间维度（如订单日期、账单月份）和数值维度（如流水号、金额区间、ID 段）是最典型的两类分区键。

理解范围分区，关键要抓住三点：语法结构、边界定义规则、分区维护动作。语法决定表怎么建，边界决定数据怎么落，维护动作决定数据怎么滚动、归档和清理。

一、语法骨架与边界定义

范围分区在建表时通过 PARTITION BY RANGE 声明，每个分区用 VALUES LESS THAN 指定上界。Oracle 的区间是“小于上界”，即 [下界, 上界)，下界由前一个分区的上界隐式确定。第一个分区没有显式下界，最后一个分区通常用 MAXVALUE 兜底，防止插入超出范围的数据时报 ORA-14400。

CREATE TABLE t_order (
  order_id    NUMBER(18),
  order_date  DATE,
  amount      NUMBER(12,2),
  status      VARCHAR2(20)
)
PARTITION BY RANGE (order_date) (
  PARTITION p202401 VALUES LESS THAN (DATE '2024-02-01'),
  PARTITION p202402 VALUES LESS THAN (DATE '2024-03-01'),
  PARTITION p202403 VALUES LESS THAN (DATE '2024-04-01'),
  PARTITION pmax    VALUES LESS THAN (MAXVALUE)
);

这里 p202401 实际承载的是 2024-01-01 00:00:00 到 2024-01-31 23:59:59 的数据，因为上界是 2024-02-01。若使用 DATE 类型，边界精确到秒；若使用 TIMESTAMP，则精确到小数秒。数值分区同理，例如按 order_id 划分：VALUES LESS THAN (1000000)、VALUES LESS THAN (2000000)，区间含义完全一致。

边界定义的一个常见误区：把 VALUES LESS THAN (DATE '2024-02-01') 误认为包含 2 月 1 日当天。实际上 2 月 1 日 00:00:00 已经落入下一个分区。若业务按“自然月”归档，建议统一用“下月 1 日 00:00:00”作为上界，语义最清晰。

二、滚动窗口管理：ADD / DROP / SPLIT / MERGE

范围分区真正的价值在于“滚动窗口”。以按月分区、保留最近 12 个月为例，每月需要新增一个未来分区，同时把最老的分区移出窗口。核心 DDL 如下：

-- 新增下个月分区
ALTER TABLE t_order ADD PARTITION p202404
  VALUES LESS THAN (DATE '2024-05-01');

-- 拆分 MAXVALUE 分区（若建表时用了 pmax）
ALTER TABLE t_order SPLIT PARTITION pmax AT (DATE '2024-05-01')
  INTO (PARTITION p202404, PARTITION pmax);

-- 合并相邻小分区
ALTER TABLE t_order MERGE PARTITIONS p202401, p202402 INTO p2024Q1;

-- 删除最老分区（数据一并删除）
ALTER TABLE t_order DROP PARTITION p202301 UPDATE GLOBAL INDEXES;

几个工程要点：第一，若表上有全局索引，DROP/TRUNCATE 分区时必须加 UPDATE GLOBAL INDEXES，否则全局索引会失效（UNUSABLE），后续查询报错。第二，ADD PARTITION 只能追加在末尾，不能插入中间；要在中间插入必须用 SPLIT。第三，MERGE 只能合并相邻分区，且合并后分区名取第一个分区名。第四，DROP 是 DDL，会隐式提交，且删除的数据无法通过闪回查询恢复（除非有备份或闪回数据库）。

三、历史数据归档与清理

“归档”和“清理”是两件事：归档是把冷数据搬到低成本存储或历史表，清理是把不再需要的数据从主表移除。范围分区让这两件事都变成“分区级操作”，而不是逐行 DELETE。

推荐的做法是先交换、再删除：用 EXCHANGE PARTITION 把待归档分区与一张普通表互换，普通表就承载了该分区的全部数据，可以导出、压缩、搬到历史库；主表对应分区变成空分区，再 DROP 或 TRUNCATE 即可。这样归档过程对主表几乎无锁，且可回退。

-- 1. 建同结构普通表
CREATE TABLE t_order_arch_202301 AS SELECT * FROM t_order WHERE 1=0;

-- 2. 交换分区（含数据）
ALTER TABLE t_order EXCHANGE PARTITION p202301
  WITH TABLE t_order_arch_202301;

-- 3. 归档表导出/迁移后，清理主表空分区
ALTER TABLE t_order DROP PARTITION p202301 UPDATE GLOBAL INDEXES;

清理策略上，TRUNCATE PARTITION 比 DROP PARTITION 更温和：它保留分区定义，只清空数据，适合“分区结构要保留、数据要清掉”的场景；DROP PARTITION 则连分区定义一起删除，适合滚动窗口。两者都是 DDL，都会隐式提交，执行前务必确认分区名和业务影响。

不要用 DELETE FROM t_order WHERE order_date < ... 做大规模清理。逐行 DELETE 会产生大量 undo/redo、触发索引维护、可能撑爆 undo 表空间，且无法像分区操作那样快速释放空间。范围分区的清理，应优先走分区级 DDL。

四、分区裁剪与执行计划验证

范围分区能否提升查询性能，取决于分区裁剪（Partition Pruning）是否生效。当 WHERE 条件直接作用于分区键、且条件为常量或可推导的区间时，优化器会在解析阶段排除无关分区。例如 WHERE order_date >= DATE '2024-02-01' AND order_date < DATE '2024-03-01' 只会访问 p202402。

验证方法：用 EXPLAIN PLAN 或 DBMS_XPLAN.DISPLAY 查看执行计划中的 Pstart/Pstop 列，它们表示实际访问的分区号范围。若 Pstart/Pstop 显示为 1 到分区总数，说明裁剪未生效，常见原因是：对分区键使用了函数（如 TO_CHAR(order_date,'YYYYMM')）、隐式类型转换、或绑定变量导致优化器无法确定区间。

范围分区滚动窗口与归档清理链路 时间 p202301 1月（待归档） p202302 2月 p202303 3月 p202404 4月（新增） pmax MAXVALUE 兜底 窗口向右滚动：ADD 新分区 → 老分区移出 ① EXCHANGE PARTITION p202301 ↔ 归档普通表 ② 归档表导出/迁移 Data Pump / 历史库 ③ DROP / TRUNCATE 清理主表空分区 关键判断依据 • 边界：VALUES LESS THAN 为“小于上界”，区间左闭右开，MAXVALUE 兜底防 ORA-14400 • 裁剪：EXPLAIN PLAN 查看 Pstart/Pstop，避免对分区键使用函数或隐式转换 • 索引：DROP/TRUNCATE 分区时加 UPDATE GLOBAL INDEXES，防止全局索引失效

最后给出一个可复用的滚动窗口维护清单，按“每月执行一次”的节奏组织：

步骤操作关键命令/检查点
1新增未来分区ADD PARTITION 或 SPLIT pmax，上界取下月 1 日
2确认待归档分区查询 USER_TAB_PARTITIONS，核对分区名与 high_value
3交换归档EXCHANGE PARTITION ... WITH TABLE
4导出/迁移归档表Data Pump 或历史库同步，校验行数一致
5清理主表分区DROP PARTITION ... UPDATE GLOBAL INDEXES
6验证检查全局索引状态、执行计划 Pstart/Pstop、分区总数

范围分区的实战要点可以概括为：边界用“小于上界”定义，滚动靠 ADD/SPLIT/DROP，归档先 EXCHANGE 再清理，性能靠分区裁剪验证。把这四件事做扎实，时间维度和数值维度的大表治理就有了稳定的骨架。

← 上一章 📖 返回目录 下一章 →