自动分区+冷分区迁移+压缩：Oracle流水表存储与查询性能优化实践

接手过一张按天自动分区的流水表之后，我是真的体会到了“自动分区很省心，但省不了心”。自动分区帮你把“每个月/每天手工建分区”的重复劳动干掉了，可它不会替你考虑：旧分区还在昂贵的存储上躺着，查询还是会扫过大量历史块，表空间和备份体积一年比一年大。今天这篇想聊的，就是在这个基础上再往前走一步——用自动分区 + 自动冷分区迁移 + 冷分区压缩的组合，把查询性能和存储压力一起理顺。

这篇文章适合正在用或准备用 Oracle 分区表做业务流水、订单、日志、审计类数据的 DBA、数据架构师和运维同学。不需要太高门槛，只要你会建分区、会写存储过程调度，就能照着搭出来。我会把设计思路、参数选择、脚本模板、实测收益以及我踩过的坑都放到后面，保证都是可以直接落地的东西。

1. 自动分区已经很香，为什么还要折腾冷分区

1.1 自动分区到底省了什么事

先说清楚自动分区是什么。Oracle 从 11g 开始提供 Interval Partitioning，我们通常叫它间隔分区或者直接叫自动分区。它做的事情很简单：你告诉 Oracle 按什么间隔生成新分区，比如按天、按月，当插入的数据超过当前最大分区边界时，Oracle 自动创建下一个分区，不用你半夜爬起来手工ALTER TABLE ADD PARTITION。

常见的建表方式长这样：

CREATE TABLE sales ( id NUMBER(12), sale_date DATE, amount NUMBER(10,2) ) PARTITION BY RANGE (sale_date) INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) ( PARTITION p_2024_01 VALUES LESS THAN (TO_DATE('2024-02-01', 'YYYY-MM-DD')) );

这样一张表，以后每个月的数据进来，分区自动“长”出来。没有 Interval 之前，每月忘了建分区，业务插入就会报 ORA-14400，值班电话响一夜。自动分区把这一类维护负担基本清零了，这也是它一出来就广受欢迎的原因。

但是注意，自动分区只解决了“分区自动生成”这一个问题。数据增长带来的存储消耗、查询性能退化、备份恢复变慢，它一个都没管。分区照样全都在同一个默认表空间里，旧的新的一个待遇。数据量小的时候没感觉，攒到 TB 级之后问题就出来了。

1.2 冷热不分带来的两个麻烦

我拿一张实际维护过的业务流水表举例。这张表按天分区，跑了大概两年，总量 1.2TB 左右。业务报表和前台查询真正高频访问的，只有最近 3 个月的数据，预计 60 到 80GB。但整张表 1.2TB 全放在同一套存储上，历史分区和热分区混在一起，带来的问题是实打实的：

第一个是查询性能。Oracle 的分区裁剪确实能根据条件跳过不相关的分区，可只要查询范围落在历史区间——比如做半年汇总、某个旧订单追溯——它就要读那些完全没有压缩、字段宽、行密度低的分区。数据块多，物理读大，SQL 跑得慢。更隐蔽的是，SGA 里的 buffer cache 会被这些历史数据块反复冲击，热分区原本能缓存的块被挤出去，整个系统的命中率都会波动。

第二个是存储成本。热数据放在 SSD 上是为了响应速度，可历史数据明明一年也查不了几次，凭什么也占着同样昂贵的热存储？表空间文件大，RMAN 备份时间拉长，恢复到测试环境的时间也跟着变长，灾难恢复演练成本直线上升。这些都是不分区冷热就永远解决不了的事。

你可以把自动分区想象成一个只会帮你盖仓库的库管员——仓库越盖越多，但货物堆得乱七八糟，新货旧货全都堆在门口黄金位置。冷分区自动迁移 + 压缩，才是后面真正需要花心思布置的“仓库分区管理方案”。

2. 冷分区的定义与自动化方案设计

2.1 先把“冷”量化，再谈自动

做冷热分离最怕的就是拍脑袋。什么叫冷分区？不是你看着像冷就叫冷，要有一个能被程序识别和执行的量化标准。我习惯用两条规则来定义：

• 该分区已经不再接受高频写入，或者从数据写入逻辑上已经“关闭”。
• 该分区距离当前时间超过一定的保留窗口，比如 180 天。

第一条非常重要。为什么要先看写入是否关闭？因为如果分区还在持续写入，你把它搬到冷表空间甚至压缩了，后续再来 DML，行迁移、锁等待、块分裂这些麻烦会一起找上门。对于流水数据，往往是当月分区会被回溯补录、修正，超过几个月之后写入量才真正趋近于零。所以冷热阈值不要只盯着查询频率，还要看业务写入习惯。

我在实际项目里常用的窗口是：最近 6 个月以内的分区留在热表空间，超过 6 个月开始进入“待处理”队列。这个窗口不是拍脑袋定的，而是因为业务方说“超过 6 个月的流水基本只有财务审计会查，而且都是月度汇总查询”，写入也早就停了。作为 DBA，你需要和业务确认的其实是两件事：历史数据还要不要被在线查询？大概多久之后查询频率可以忽略？确认完这两点，阈值自然就出来了。

2.2 同表迁移还是分区交换，两条路怎么选

确定哪些分区是冷分区之后，处理方法有两条路，很多人拿不准。一条是直接在原表里把分区移动到冷表空间，一条是通过分区交换把数据换到独立的归档表。

同表迁移的做法是：

ALTER TABLE sales MOVE PARTITION p_202309 TABLESPACE ts_sales_cold COMPRESS ONLINE UPDATE INDEXES;

这条命令执行完，分区还是sales表的一部分，业务查询完全透明，不需要改任何 SQL。适合历史数据还需要继续被在线查询、统一报表访问的场景。缺点是这个分区仍然属于生产表，表的整体管理和备份策略不能彻底分离。

分区交换的做法是提前建一张结构相同的归档表，然后把要归档的分区换出去：

CREATE TABLE sales_arch_202309 AS SELECT * FROM sales WHERE 1=0; ALTER TABLE sales EXCHANGE PARTITION p_202309 WITH TABLE sales_arch_202309 INCLUDING INDEXES;

交换之后，数据从主表挪到了归档表。归档表可以单独放到廉价存储，甚至改成只读表空间，这样备份策略、恢复粒度都能完全独立。代价是业务查询历史数据时得改访问路径——要么走视图把两张表 union 起来，要么应用层知道去查归档表，改造工作量比同表迁移大。

我的选择逻辑很直接：如果业务方强烈要求“所有历史数据都在同一张表里查”，走同表 MOVE；如果数据基本不会被在线查询，只是偶尔回溯，走交换 + 只读表空间，这是成本最低的终态。下面这张表总结了两条路的差异，方便你对应自己的场景：

2.3 用 DBMS_SCHEDULER 搭一个自动处理框架

冷分区迁移如果靠人工每月执行一次，早晚会忘，而且一旦处理到一半出错，恢复起来很痛苦。我建议直接用一个定期 Job 把流程自动化：每周日凌晨 1 点跑一次，由存储过程扫描分区字典，找出所有满足“超过 6 个月 + 不在冷表空间 + 未压缩”条件的分区，逐个执行移动和压缩，同时把每个分区的处理结果写到日志表里。

为什么要每周而不是每天？因为 MOVE PARTITION 再快，也是一个重建段的过程，会产生大量 I/O 和 redo。考虑到备份窗口和业务高峰，一周一次已经足够。处理数量上也要做限制，不要一次把所有历史分区全部搬完，建议每个周期最多处理 5 到 10 个分区，细水长流地把历史包袱消化掉。第一次上线时如果积压太多，可以先写一个独立脚本来个“大扫除”，日常 Job 只负责增量处理。

3. 压缩冷分区的技术选型与实测

3.1 普通环境可用的压缩方案怎么选

Oracle 的压缩方案很容易把人绕晕，因为不同版本、不同选项的语法长得太像了。先帮你把主流的几种拉通一下：

• 基础表压缩（Basic Compression）：老的COMPRESS语法，对应后来的ROW STORE COMPRESS BASIC。它只在直接路径加载时压缩数据，压缩率高，但压缩后分区对后续 DML 很不友好，任何写操作都可能带来大量行迁移。对只读冷数据来说，这恰恰是优点——不写就不怕。
• 高级行压缩（Advanced Row Compression）：早期叫 OLTP 压缩，语法可以是COMPRESS FOR OLTP，也可以在新版本里写成ROW STORE COMPRESS ADVANCED。它允许压缩后的数据继续执行 DML，兼顾了读写场景，压缩率通常比基础压缩低一些，而且要额外检查许可证。
• 混合列压缩（HCC）：这是 Exadata 等环境的强项，普通非 Exadata 数据库其实用不上。所以如果你不在 Exadata 上，就不要在 HCC 上花时间，把重心放在行压缩就够了。

冷分区选择哪个？我的答案很明确：如果分区已经彻底只读，优先选基础压缩，压缩率最高，存储成本降得最明显。如果分区偶尔还会有零星修正，那就老实选高级行压缩，别为了省那点空间给自己埋雷。注意基础压缩会对后续 DML 产生负面影响，所以压缩前务必确认该分区已经不再有写入。

3.2 MOVE PARTITION COMPRESS 实战

确定了压缩方案，实操就围绕一条核心 DDL 展开。下面是我在 12c/19c 环境上常用的写法：

ALTER TABLE sales MOVE PARTITION p_202309 TABLESPACE ts_sales_cold COMPRESS ONLINE UPDATE INDEXES PARALLEL 4;

这里几个参数都值得展开说一下。

ONLINE表示在线移动分区，执行期间允许业务继续对这个分区进行 DML。虽然 Online Move 也会产生额外开销，但比直接锁表要温和太多。第一次踩坑经历让我对没有ONLINE的 MOVE 记忆犹新——晚上跑批正好撞上业务补数据，DDL 直接阻塞，业务会话全堵在队列里。如果你确定执行窗口绝对是低峰，不加 ONLINE 可以跑得更快，但建议默认还是加上，用可控的耗时换安全的并发。

UPDATE INDEXES是必须写的。分区上的全局索引在 MOVE 之后如果不维护，会直接变成 UNUSABLE，比锁表还可怕——查询要么报错，要么走全表扫描。加上这个参数，Oracle 会在移动过程中同步维护全局索引，额外时间要心里有数。如果表上有多个全局索引，这一步的开销会明显变大。

PARALLEL 4是并行度，目的是缩短 MOVE 的时间。但我建议别一上来就开 8 甚至 16，生产环境要考虑 I/O 压力。我一般从 4 起步，观察系统负载再调整。并行度过高时，除了磁盘 I/O，还会放大 redo 生成量，压缩过程产生的 redo 本来就比普通 Move 要多。

还有一点很多人忽略：MOVE PARTITION 会在目标表空间里重建一个完整的新段，所以ts_sales_cold至少要留出目标分区大小的剩余空间。分区越大，对空间的要求越苛刻。别忘了压缩之后段会变小，但压缩是边读边写边压缩，过程里新旧段是同时存在的。

3.3 压缩带来的收益和代价，用数据说话

压缩到底值不值，不能靠感觉，要拿数据说话。我挑了一个单月 1.2 亿行的销售明细分区做过对比，日期范围一个月，字段包括金额、渠道、商品编码等，平均行长偏大。压缩前的数据量大概 12GB，使用基础压缩后降到 4.3GB，压缩率超过 60%。

这个收益是实实在在的，尤其对存储在配额紧张、按容量计费或者备份时间吃紧的环境，节省非常可观。而且压缩后的数据块能容纳的行数变多，全分区聚合类查询需要扫描的块数明显减少。我对同一个分区跑过一次SELECT SUM(amount) FROM sales WHERE sale_date BETWEEN ...，压缩前全分区扫描大约 38 秒，压缩后 13 秒，I/O 层面提升明显。

但我也要说清楚另一面：压缩数据在读取时需要 CPU 参与解压。如果系统瓶颈本来就在 CPU，压缩后查询性能未必会提升，甚至可能下降。我遇到过一台老旧的库存数据库，CPU 常年 90% 以上，压缩后单行点查反而变慢，最后又改回了不压缩。压缩不是银弹，它更适用于数据仓库、分析型查询和 I/O 密集场景，而不是高频小事务点查。所以在决定给哪些表压缩之前，先想清楚你系统的瓶颈到底在哪。

4. 自动识别+自动压缩的完整落地脚本

4.1 分区命名规范和自动识别逻辑

自动化的前提是脚本能可靠地认出“哪个分区是旧的”。最靠谱的做法不是去解析HIGH_VALUE，那列存的是TO_DATE('2023-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', ...)这种带引号的字符串，解析起来又麻烦又容易踩格式坑。我的建议是建表时就统一分区命名规则：月分区就叫p_YYYYMM，日分区就叫p_YYYYMMDD。这样判断分区年龄可以直接从分区名里截取年月，逻辑一眼就能看懂。

自动识别要处理的对象需要同时满足几个条件：分区属于目标表、压缩状态还是 DISABLED、当前表空间是热表空间、分区名的年月早于阈值。查询语句大致长这样：

SELECT table_owner, table_name, partition_name FROM dba_tab_partitions WHERE table_owner = 'APP' AND table_name = 'SALES' AND compression = 'DISABLED' AND tablespace_name = 'TS_SALES_HOT' AND TO_NUMBER(SUBSTR(partition_name, 3, 6)) <= TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -6), 'YYYYMM')) ORDER BY partition_name;

注意SUBSTR(partition_name, 3, 6)只有在分区名确实是p_202309这种格式时才成立。如果你维护的老表分区名不是这个规则，要么先统一改名，要么写一个函数专门解析HIGH_VALUE。两种方案我都用过，强烈建议前者，规范的命名比任何解析函数都省心。

4.2 核心存储过程：识别、迁移、压缩、记录日志

自动处理的存储过程并不复杂，核心思路就是遍历上面的结果集，逐个执行动态 SQL，然后把结果写进日志表。我贴一个可以直接改改就用的精简版：

CREATE OR REPLACE PROCEDURE proc_cold_partition IS v_cnt NUMBER := 0; v_start DATE; v_end DATE; BEGIN FOR r IN ( SELECT table_owner, table_name, partition_name FROM dba_tab_partitions WHERE table_owner = 'APP' AND table_name = 'SALES' AND compression = 'DISABLED' AND tablespace_name = 'TS_SALES_HOT' AND TO_NUMBER(SUBSTR(partition_name, 3, 6)) <= TO_NUMBER(TO_CHAR(ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -6), 'YYYYMM')) ORDER BY partition_name ) LOOP EXIT WHEN v_cnt >= 5; v_start := SYSDATE; BEGIN EXECUTE IMMEDIATE 'ALTER TABLE ' || r.table_owner || '.' || r.table_name || ' MOVE PARTITION ' || r.partition_name || ' TABLESPACE ts_sales_cold COMPRESS ONLINE UPDATE INDEXES PARALLEL 4'; v_end := SYSDATE; INSERT INTO partition_move_log(schema_name, table_name, partition_name, start_time, end_time, status, error_msg) VALUES (r.table_owner, r.table_name, r.partition_name, v_start, v_end, 'SUCCESS', NULL); EXCEPTION WHEN OTHERS THEN v_end := SYSDATE; INSERT INTO partition_move_log(schema_name, table_name, partition_name, start_time, end_time, status, error_msg) VALUES (r.table_owner, r.table_name, r.partition_name, v_start, v_end, 'FAILED', SQLERRM); -- 记录失败后继续下一个，不阻断整体批次 END; COMMIT; v_cnt := v_cnt + 1; END LOOP; END; /

几个设计细节说明一下：

• EXIT WHEN v_cnt >= 5控制每个批次最多处理 5 个分区。这个数字你可以按业务量和窗口时间自己调，宁可每次少做，也不要让 Job 跑不完。
• EXCEPTION WHEN OTHERS THEN把失败隔离到单分区级别，一个分区迁移失败不会拖垮整个批次。错误信息记录在日志表里，第二天巡检时一眼就能看到。
• 每条执行完立刻COMMIT，避免日志丢失，也避免长事务占着 UNDO。
• 如果你要压缩的不是基本压缩，而是高级行压缩，只需要把MOVE PARTITION ... COMPRESS改成MOVE PARTITION ... COMPRESS FOR OLTP即可，其余逻辑不需要动。

日志表partition_move_log按需建，至少包含start_time、end_time、status、error_msg这几个字段，后面验证效果和排查问题全靠它。

4.3 运行验证：怎么看效果、怎么日常巡检

脚本上线之后，不能只看日志表里写 SUCCESS 就以为完事了。我每次处理完一批，都会用一条 SQL 确认压缩和表空间状态是不是真的变了：

SELECT partition_name, tablespace_name, compression, compress_for FROM dba_tab_partitions WHERE table_owner = 'APP' AND table_name = 'SALES' ORDER BY partition_name;

这里compression会显示ENABLED或DISABLED，tablespace_name能确认分区是否已经落到了TS_SALES_COLD。两个条件都满足，说明这个分区真正完成了冷化。

压缩只是第一步，别忘了统计信息。压缩会改变数据块的密度和行数分布，旧的统计信息可能让优化器误判。我习惯在分区压缩完成后立即重新收集这个分区的统计信息：

EXEC DBMS_STATS.GATHER_TABLE_STATS('APP', 'SALES', PARTNAME => 'p_202309', GRANULARITY => 'PARTITION');

做完全部处理，我还会隔几天对比一次查询的物理读和逻辑读，确认热分区查询是否变快、冷分区聚合查询是否受益。数据说话，以免自我感觉良好。

5. 我踩过的坑，和最终沉淀的检查清单

5.1 六个真实踩坑记录

第一个坑是没加 ONLINE。第一次在测试环境执行速度挺满意，一到生产就露馅——晚上正好有批处理在补录数据，MOVE 拿不到锁，把在线会话全卡住了。后来我无论什么情况都默认带 ONLINE，只有确认绝对无业务时才考虑去掉。

第二个坑是不写 UPDATE INDEXES，全局索引全部 UNUSABLE。当时表上有两个全局索引，MOVE 完一查索引状态直接傻眼，业务查询马上报错。后来所有 MOVE 都强制UPDATE INDEXES，哪怕执行时间长一点也认。

第三个坑是空间评估不足。冷表空间剩余空间看着够，但 MOVE 过程需要同时容纳新旧两个段，实际才跑一半就报 ORA-01652 中断了。从那以后我在批量处理前会先看dba_free_space，确认冷表空间至少有目标分区两倍的余量再做。

第四个坑是压缩后没刷统计信息。压缩完第二天一条月度汇总 SQL 执行计划突然变成全表扫描，排查半天，最终发现是统计信息没更新，优化器拿旧的行数去估算，走了岔路。后来压缩和GATHER_TABLE_STATS成对操作，再没出过类似问题。

第五个坑要提醒 CPU 瓶颈的系统。压缩确实省了存储、减少了 I/O，但换来的是 CPU 解压开销。我在一台 CPU 常年跑满的主机上做过对比，点查性能反而下降。若你的系统瓶颈在 CPU，请先解决 CPU 再谈压缩，或者干脆只压缩真正完全不查的归档分区。

第六个坑是压缩时机选得太早。有一张订单表，我按 3 个月窗口压缩了一个即将结束的月份分区，结果业务财务调整又往里面补了几十万行。基本压缩的分区一遇到 DML，行迁移量非常大，补数任务跑了很久。后来我把压缩窗口拉长到 6 个月，写入基本关闭才动手，这类问题就消失了。

5.2 业务场景适用性判断

并不是所有表都适合做冷分区压缩。我的判断标准很简单，先问三个问题：这张表是否按时间增长？旧数据是否几乎只读？历史数据是否需要在线查询？三个问题答案都是“是”，那冷分区压缩方案就是加分项。订单表、流水表、日志表、审计表都属于标准画像。

反过来，如果一张表虽然按时间分区，但历史分区经常被更新，或者查询基本都以主键单行点查为主，压缩带来的收益就很有限，反而要承受 DML 行迁移和 CPU 解压的代价。这类表不要为了“别人都在做”而盲目上压缩，状态健康比压缩率更重要。

5.3 可以先从哪些表开始试点

新方案切忌一上来就铺全量。我会选一张体量中等、月分区 5 到 20GB、历史分区确认只读的表做试点，比如某条日志表或者次要流水表。先跑一个压缩批次，观察 MOVE 耗时、redo 增量、冷表空间占用、压缩率和近期 SQL 有没有回退，全都符合预期后，再逐步推广到核心大表。整个过程控制在一个双休日内能完成的处理量，避免压缩任务撞上业务高峰和备份窗口。

我个人在实际操作中还会把一个技巧记在心里：冷热处理分成两阶段执行。第一阶段先把超窗分区 MOVE 到冷表空间，但不急着压缩；隔一两个批次之后，确认这些分区在冷表空间运行稳定、确实没有 DML 了，再执行 COMPRESS。这样把“搬迁”和“压缩”两个风险拆开，即使压缩阶段出问题，数据也已经离开了热存储，影响范围小得多。最后再提醒一句：压缩之后一定要重新收集统计信息，这一步漏了，前面的功夫可能白费。