1. 自动分区解决了“写”的问题但“存”和“查”仍然在失控1.1 自动分区到底帮我们做了什么很多DBA一听到自动分区就以为万事大吉实际它只解决了“新数据来了怎么放”的问题。Oracle从11g开始提供INTERVAL分区12.2以后又加入了AUTOMATIC LIST分区和AUTOMATIC HASH分区核心就是当数据超出既有分区边界时数据库自动创建下一个分区不再需要我们手工维护一堆空分区或者担心插入报错ORA-14400。我见过不少朋友上了自动分区以后很兴奋觉得运维工作量下来了。没错日增几百万笔的流水表不用再每月去手动加分区了确实省心。但注意自动分区只是让“新分区按既定规则长出来”它压根不管这些分区里面装的数据未来是热是冷。也就是说它解决了写入路径上的可用性问题却没有解决存储膨胀和访问性能这两个真正让人头疼的事情。1.2 被忽略的数据冷热分化你只要把一个表按时间分区跑上一两年就会看到极其典型的冷热分化最近三个月的数据被频繁查询三个月以前的数据基本没什么人碰。但分区层面完全没有区别对待——历史分区和新分区共享相同的表空间、相同的压缩策略、相同的物理块密度。举个我实际处理过的例子。一张订单流水表按月INTERVAL分区。表总大小312GB其中最近三个月的数据大约45GB剩下的267GB全是超过六个月的历史数据。这些历史数据一个月也未必被查一次却同样占用昂贵的高性能存储同样在每次全表扫描时被完整读完。更尴尬的是由于历史分区太大查询优化器有时候宁可走全分区扫描也不走索引因为全表扫描的成本随着数据膨胀变得越来越有“竞争力”。这里要明确一个认知自动分区只是把数据切碎了切碎不等于优化。真正的优化在于数据生命周期管理也就是对冷数据采取与热数据完全不同的存储和压缩策略。这也是这篇文章要展开的核心思路——在自动分区已经落地的基础上再叠加一个自动冷分区与压缩的机制让冷数据真正“躺平”。2. 冷分区怎么做才算“真的冷”判定阈值和降级存储缺一不可2.1 冷分区的判定三种常用口径首先要回答一个问题什么样的分区算冷分区Oracle没有内置的“温度计”直接告诉你说哪个分区冷所以需要我们根据业务特点自己定规则。实践中我见过三种主流口径时间阈值法最简单也最常用。规定超过N个月的分区一律视为冷分区。比如订单表只保留最近3个月为热区更早的全部转冷。变更率法根据分区的DML活跃度来判断。如果连续N天没有INSERT和UPDATE说明这个分区已经进入只读状态可以降级了。Oracle里可以通过视图观察段的活动情况也可以通过审计或统计信息来辅助。业务口径法有些表不能用统一时间一刀切。比如订单状态是“已完成”且超过某时间才能归档比如日志表按“保留90天”来清理。这种规则通常要写进配置表由DBA根据业务变化调整。说实话时间阈值法在流水类、日志类表上最可靠也最容易自动化。业务上几乎都认可“最近几个月的需要在线查询更早的能查到就行慢一点没关系”。如果你是做金融、电商、运营商这类数据的大概率也会落到这个方案上。2.2 冷热分区的物理隔离别让小池子拖累大池子判定了冷分区下一步是把冷数据从原有的高性能表空间挪出去。我强烈建议在物理上把冷热分区分开而不是只在逻辑上标记一下。原因有几层冷数据的特点是“极少写、偶尔读”完全没必要放在高性能存储上。把冷分区移动到独立表空间后可以做更激进的压缩压缩后的段大小变化不会影响热分区的空间估算。如果将来想对整个冷数据表空间做只读处理、备份策略调整或者做独立的存储分层也会方便很多。在我们的方案里通常规划两个表空间TBS_ORDER_HOT用于最近N个月的在线分区TBS_ORDER_COLD用于历史冷分区。冷表空间可以用廉价的大容量磁盘甚至在云上就是标准存储而不是性能型存储。物理移动分区最常用的命令是ALTER TABLE order_his MOVE PARTITION p202401 TABLESPACE tbs_order_cold COMPRESS FOR OLTP PARALLEL 4 UPDATE GLOBAL INDEXES;这条命令做了三件事把分区数据重写到新表空间、按OLTP压缩重写存储、同时维护全局索引。如果不带UPDATE GLOBAL INDEXES移动完分区以后全局索引会变成UNUSABLE这个坑后面细说。还有一个重要选项ONLINE关键字。从12.2开始Oracle支持在线移动分区ALTER TABLE order_his MOVE PARTITION p202401 TABLESPACE tbs_order_cold COMPRESS FOR OLTP ONLINE UPDATE GLOBAL INDEXES;加上ONLINE之后移动期间原分区上的DML不会被阻塞这对7乘24小时的生产系统非常关键。我一般只在业务低峰期跑批量冷处理作业但也会把ONLINE加上权当双保险。2.3 大批量归档时用分区交换如果冷数据归档是一次性的、且数据量特别大用MOVE PARTITION逐个搬可能太慢。这时候可以考虑ALTER TABLE EXCHANGE PARTITION。它的思路是先把冷分区和一个结构相同的空表做交换这个空表就变成了主表里的正式分区而原来主表里的那个分区则变成了独立表之后你可以对这个独立表任意处理——压缩、移动表空间、甚至直接DROP。-- 创建结构一致的目标表 CREATE TABLE order_his_ext ( ... ); -- 把主表里的 p202312 分区换出来 ALTER TABLE order_his EXCHANGE PARTITION p202312 WITH TABLE order_his_ext; -- 之后可以对新表做压缩搬迁 ALTER TABLE order_his_ext MOVE TABLESPACE tbs_order_cold COMPRESS FOR QUERY LOW;交换分区的速度取决于元数据更新而非数据搬运所以在海量数据场景下优势明显。但它要求操作者对分区结构和约束非常清楚我建议在自动化脚本里把交换作为“备选路径”常规跑批还是以MOVE PARTITION为主避免复杂度上升。3. 压缩冷分区到底该选哪一档先看懂Oracle压缩的等级再说3.1 Oracle压缩选项一张表看清压缩不是越猛越好关键是找到适合冷数据的那个档位。Oracle主流压缩方式如下压缩方式适合场景典型压缩比说明COMPRESS BASIC数据仓库批量加载、只读数据2-4倍基本压缩写路径开销小读取有解压CPU成本COMPRESS FOR OLTP在线交易混合读写2-4倍需要高级压缩选项授权压缩和解压开销可控COMPRESS FOR QUERY LOW只读、以查询为主5-10倍HCC压缩道路通常需要存储层配合COMPRESS FOR ARCHIVE HIGH归档不需频繁访问10-15倍以上HCC压缩CPU开销最大适合极冷数据注意一点COMPRESS FOR OLTP属于Oracle高级压缩选件的一部分购买许可的时候要确认一下。HCC混合列式压缩最早是Exadata存储上的特性后来在特定版本和许可组合下也能在非Exadata存储上使用但性能表现和压缩比会有差异。如果你的环境只是普通PC服务器加中小企业版稳妥的选择是普通用户存储上用OLTP压缩Exadata上再考虑QUERY LOW或ARCHIVE HIGH。3.2 为什么压缩能同时改善查询性能和存储很多刚入门的DBA有个误解认为压缩只是省空间查询反而会变慢。实际不完全对。压缩后每个Block里面装的行数变多了原本需要读取10个块才能扫完的数据压缩后可能只需要读2-3个块。I/O大幅减少这对顺序扫描和全分区扫描的查询来说性能提升非常明显尤其是冷分区经常要做历史统计数据汇总的场景。我举一个量化例子同样的订单表某个冷分区原本占用22GB用COMPRESS FOR QUERY LOW压缩后降到4.5GB。应用程序对这个分区做月度汇总压缩前需要读22GB的物理I/O跑一次约75秒压缩后只需要读4.5GB物理I/O加上解压缩的CPU开销实际耗时约22秒。查询性能提升超过3倍存储节省接近80%。代价是CPU消耗更高但冷数据查询频率低这点CPU基本可以忽略。3.3 压缩前必须想清楚的几个前提冷分区压缩不是把命令一扔就完事有几个前提条件需要先确认该分区确实是只读或极少更新的否则每次UPDATE都要解压再重压缩严重拖慢DML性能。LOB字段压缩要单独考虑。普通行压缩对LOB的压缩能力有限如果冷分区里包含大量CLOB/BLOB建议单独评估是否开启SECUREFILE压缩或者干脆延续行压缩就好。压缩是段级操作一旦对某个分区执行了压缩后续新插入的数据也会按压缩格式存储。所以不要对热分区执行压缩否则写路径开销立刻上来。统计信息收集要在压缩完成之后重新跑一遍。压缩会大幅度改变段大小、块密度和统计分布不重新收集统计信息优化器会基于压缩前的统计做错误估算。4. 把“自动”彻底落地一套可上生产的冷分区调度脚本4.1 设计思路与整体流程前面讲了原理和选型这一节给出一个可以实际部署的自动化方案。目标是每天凌晨跑一个PL/SQL批处理作业自动找出“年龄超过N个月”的分区把还没压缩、还在热表空间的冷分区自动迁移并压缩。整个流程分四步读规则从配置表读取冷却阈值比如3个月这样调整规则不用改代码。扫分区遍历目标表的所有分区解析每个分区的高值边界结合当前日期判断哪些分区达到冷却条件。迁移压缩对符合条件的分区逐个执行MOVE PARTITION带上表空间、压缩级别和索引维护参数。收尾验证结束后重新收集统计信息并检查索引状态、分区大小输出处理报告。4.2 识别冷分区的关键解析分区边界自动化的核心难点不在执行MOVE而在“准确判断哪些分区已经凉了”。Oracle的数据字典里分区的上界值存在ALL_TAB_PARTITIONS.HIGH_VALUE但它是一个长字符串类型的表达式。如果是按月范围分区我们需要把它解析成日期再比较。SELECT table_name, partition_name, high_value FROM all_tab_partitions WHERE table_name ORDER_HIS ORDER BY partition_position;HIGH_VALUE常见的形式是TO_DATE( 2024-01-01 00:00:00, SYYYY-MM-DD HH24:MI:SS, NLS_CALENDARGREGORIAN)。解析思路很简单从字符串中间抽取日期部分用TO_DATE转成DATE类型然后和阈值日期对比。如果是每个月一个分区、分区边界设置为每个月第一天那么一个分区的高值如果是2024年1月1日说明这个分区装的是2023年12月的数据。只要这个日期早于当前日期减去冷却月数就可以判定为冷分区。4.3 核心脚本示例下面给出一个可运行的核心脚本框架DECLARE v_table_name VARCHAR2(50) : ORDER_HIS; v_cold_tbs VARCHAR2(50) : TBS_ORDER_COLD; v_keep_months NUMBER : 3; v_cutoff_date DATE : ADD_MONTHS(SYSDATE, -v_keep_months); v_part_name VARCHAR2(50); v_high_val_expr LONG; v_part_date DATE; v_sql VARCHAR2(1000); BEGIN FOR p IN ( SELECT partition_name, high_value FROM user_tab_partitions WHERE table_name v_table_name ORDER BY partition_position ) LOOP -- 解析HIGH_VALUE字符串提取日期部分 v_high_val_expr : p.high_value; -- 只取形如 2024-01-01 的部分按你的分区定义调整 v_part_date : TO_DATE( SUBSTR(v_high_val_expr, INSTR(v_high_val_expr, ) 1, 10), YYYY-MM-DD ); IF v_part_date v_cutoff_date THEN BEGIN v_sql : ALTER TABLE || v_table_name || MOVE PARTITION || p.partition_name || TABLESPACE || v_cold_tbs || COMPRESS FOR OLTP || ONLINE || UPDATE GLOBAL INDEXES; EXECUTE IMMEDIATE v_sql; DBMS_OUTPUT.PUT_LINE(p.partition_name || 已迁移并压缩); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(p.partition_name || 处理失败: || SQLERRM); END; END IF; END LOOP; -- 重新收集统计信息 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME APPS, TABNAME v_table_name); END; /这段代码做了最核心的事情但真正上生产前还需要几个改造日期解析部分不要硬编码。不同的分区边界格式不同建议把解析逻辑封装成函数或者直接用正则表达式提取。每一批不要全部处理完了才收集统计信息可以分几批执行每批结束后单独收集受影响分区的统计信息。加入失败重试和巡检日志。我习惯建一张执行日志表每次跑批都记录分区名、操作时间、耗时和状态后续排查问题会省很多力气。4.4 调度时间与并发控制调度时间我建议定在凌晨1点到3点之间避开业务小高峰和备份窗口。有些人喜欢和备份错开因为MOVE PARTITION会产生大量IO与RMAN备份挤在一起会让整个存储扛不住。并发方面如果一次要搬迁十几个分区建议用PARALLEL参数控制并行度而不是手动开多个会话来并行执行多个ALTER TABLE。并行度通常设置为2到4具体看存储能力和业务容忍度。并行度过高时大量写I/O会导致磁盘队列堆积反而拖慢整体进度。5. 实测记录312GB订单表压缩到146GB历史统计查询提速两倍以上5.1 环境背景这套方案我实际在一套OLTP系统上跑过。表结构是订单流水表ORDER_HIS按月份INTERVAL分区一共61个分区5年的数据。部署前表总大小312GB其中最近3个月的热分区约45GB其余267GB是历史冷分区。原表空间全部放在同一套高性能存储上每月新增数据约15GB。实施前我对历史67个分区逐个执行了冷区判定符合冷却条件的是58个分区总计约267GB。执行策略是每个批次处理8个分区间隔10分钟全程大约2小时完成没有对业务造成任何影响。5.2 结果数据指标压缩前压缩后变化订单表总大小312GB146GB降低53%冷分区总大小267GB101GB减少约62%历史月份统计查询耗时平均75秒平均23秒提升约3倍全表扫描式宽查询耗时约310秒约155秒提升近1倍全局索引大小38GB22GB降低42%存储方面最直观312GB压到146GB释放了166GB高性能存储空间。对于云上环境这部分直接换算成成本节省。查询性能上历史数据聚合查询从75秒降到23秒原因就是扫描的物理块数量大幅减少。这里有个量化参考压缩前跑一个历史分区聚合要扫描22GB数据压缩后只扫描4.5GB虽然多了解压CPU消耗但整体时间依然明显下降。5.3 压缩后查询计划的变化这里有一个值得关注的细节压缩前优化器对于某些历史月份的聚合查询倾向于选择全分区扫描因为统计信息显示分区太大、索引的选择性优势被抵消。压缩并重新收集统计信息之后分区的高值密度变化了优化器在某些场景下反而愿意走索引路径进一步缩短了响应时间。这说明压缩冷分区除了物理层面的I/O减少还会通过统计信息间接影响执行计划的质量。5.4 一点客观提醒并不是所有查询都能变快。如果业务SQL天天以不带分区条件的模糊查询为主比如WHERE cust_id ?而不是WHERE month ? AND cust_id ?那无论怎么压缩冷分区性能改善都有限——因为你根本没有利用分区裁剪。压缩解决的是“历史数据扫描成本”的问题不是“SQL写法不走分区键”的问题。所以上线这套方案之前先把业务SQL的分区裁剪率提上去这件事优先级更高。6. 这套自动化流程里我踩过的几个容易翻车的坑6.1 全局索引UNUSABLE是最大事故源最早我跑MOVE PARTITION脚本时没带UPDATE GLOBAL INDEXES早上起来发现整张表的全局索引全部失效。查询全部走全表扫描业务直接报警。原因很简单MOVE PARTITION会物理重建分区段分区数据的物理地址全部变化基于全表的全局索引自然就失效了。以后写脚本我养成了习惯凡是涉及MOVE、TRUNCATE、EXCHANGE分区的DDL一律显式带上UPDATE GLOBAL INDEXES。虽然这会增加操作耗时全局索引需要同步更新但换来的是一夜安稳值。6.2 把分区压缩到同一个表空间等于白压还有一个容易踩的坑写MOVE PARTITION时忘了指定TABLESPACE或者指定的TABLESPACE就是原表空间。这时候压缩确实生效了段变小了但表空间里的高水位空间并没有归还回来。最终效果是表空间看起来空闲了一大截但你却无法利用这些空间因为段的新旧版本混在同一个表空间里。正确做法是像前面说的单独规划一个冷表空间。如果条件不允许新建表空间至少也要用MOVE PARTITION ... TABLESPACE 同一个表空间配合后续的SHRINK SPACE或表空间级重组但那样复杂度就上去了。能新建冷表空间直接新建省心。6.3 调度作业没考虑归档日志空间MOVE PARTITION是重操作它会产生大量的redo日志除非你在NOLOGGING模式下操作。有一段时间我把压缩脚本设到凌晨跑结果凌晨的归档日志量比平时暴增好几倍差点把归档目录撑爆。尤其是大分区建议在确认业务允许的前提下给移动操作加NOLOGGINGALTER TABLE order_his MOVE PARTITION p202401 TABLESPACE tbs_order_cold COMPRESS FOR OLTP NOLOGGING UPDATE GLOBAL INDEXES;NOLOGGING模式下产生的redo几乎为零归档日志压力骤降。代价是如果操作中途崩溃该分区需要重做所以这个参数要结合你对系统可用性的容忍度来用。6.4 别把冷热阈值写死在代码里最后一个坑是设计层面的业务变化比你想象的快。当初把“3个月”写死在PL/SQL脚本里半年后业务方说历史数据要保留6个月在线查询。改脚本本身不难但你要是把类似阈值散落在十几个存储过程里每次业务调整都要一个个翻代码很容易漏改。最好建一张配置表CREATE TABLE data_lifecycle_rule ( table_name VARCHAR2(50), keep_months NUMBER, cold_tablespace VARCHAR2(50), compress_type VARCHAR2(20), enabled VARCHAR2(1) DEFAULT Y );跑批作业启动时先读这张配置表所有规则参数都从配置里取。这样业务方提需求DBA只需UPDATE一行配置不需要重编译任何存储过程。6.5 压缩后的统计信息别忘收最后再次强调MOVE COMPRESS之后不重新收集统计信息优化器还会拿着压缩前的统计值做判断。特别是有全局索引的场景统计信息不更新可能导致执行计划严重偏离真实成本。把这个逻辑直接写进自动化收尾步骤里一天都别拖。我自己现在收到一个分区就在同一个PL/SQL块里紧接着调用DBMS_STATS.GATHER_TABLE_STATS并指定分区名不让统计信息滞后一个批次。7. 可以继续扩展的方向当前这套方案解决的是“按时间冷热分离 自动压缩”的问题架构上还留了几个可以继续深化的口子。比如冷分区的访问频率监控。现在用的是时间阈值法本质上是按业务经验来预测冷热。如果你有查询审计、AWR或者统一审计日志的数据完全可以细化成“连续N天无任何查询的分区才转冷”让系统的判断更贴近真实访问模式。再比如存储分层。冷表空间建好之后你可以结合操作系统层或者云平台的分层存储能力把冷表空间数据页自动迁移到归档存储。不用改数据库结构存储系统已经帮你完成了最后一公里的降级。还有分区交换场景下的索引处理。如果你在生产中用了EXCHANGE PARTITION做归档需要想清楚交换出去的独立表上的索引如何处理以及下一次交换回来时约束和索引是否都对得上。这些细节在自动化流程里都要提前设计而不是等出了问题再救火。总的来说自动分区管的是数据的“生长”冷分区和压缩管的才是数据的“沉淀”。两者配合起来一张表无论跑多少年在线数据量都能保持在一个稳定的区间内查询性能不会因为历史包袱逐步劣化存储成本也被控制住了。这套机制的收益会随着表的数据量增长而越来越明显越早落地越划算。 自动分区只是开始：Oracle冷数据压缩与分层管理
1. 自动分区解决了“写”的问题但“存”和“查”仍然在失控1.1 自动分区到底帮我们做了什么很多DBA一听到自动分区就以为万事大吉实际它只解决了“新数据来了怎么放”的问题。Oracle从11g开始提供INTERVAL分区12.2以后又加入了AUTOMATIC LIST分区和AUTOMATIC HASH分区核心就是当数据超出既有分区边界时数据库自动创建下一个分区不再需要我们手工维护一堆空分区或者担心插入报错ORA-14400。我见过不少朋友上了自动分区以后很兴奋觉得运维工作量下来了。没错日增几百万笔的流水表不用再每月去手动加分区了确实省心。但注意自动分区只是让“新分区按既定规则长出来”它压根不管这些分区里面装的数据未来是热是冷。也就是说它解决了写入路径上的可用性问题却没有解决存储膨胀和访问性能这两个真正让人头疼的事情。1.2 被忽略的数据冷热分化你只要把一个表按时间分区跑上一两年就会看到极其典型的冷热分化最近三个月的数据被频繁查询三个月以前的数据基本没什么人碰。但分区层面完全没有区别对待——历史分区和新分区共享相同的表空间、相同的压缩策略、相同的物理块密度。举个我实际处理过的例子。一张订单流水表按月INTERVAL分区。表总大小312GB其中最近三个月的数据大约45GB剩下的267GB全是超过六个月的历史数据。这些历史数据一个月也未必被查一次却同样占用昂贵的高性能存储同样在每次全表扫描时被完整读完。更尴尬的是由于历史分区太大查询优化器有时候宁可走全分区扫描也不走索引因为全表扫描的成本随着数据膨胀变得越来越有“竞争力”。这里要明确一个认知自动分区只是把数据切碎了切碎不等于优化。真正的优化在于数据生命周期管理也就是对冷数据采取与热数据完全不同的存储和压缩策略。这也是这篇文章要展开的核心思路——在自动分区已经落地的基础上再叠加一个自动冷分区与压缩的机制让冷数据真正“躺平”。2. 冷分区怎么做才算“真的冷”判定阈值和降级存储缺一不可2.1 冷分区的判定三种常用口径首先要回答一个问题什么样的分区算冷分区Oracle没有内置的“温度计”直接告诉你说哪个分区冷所以需要我们根据业务特点自己定规则。实践中我见过三种主流口径时间阈值法最简单也最常用。规定超过N个月的分区一律视为冷分区。比如订单表只保留最近3个月为热区更早的全部转冷。变更率法根据分区的DML活跃度来判断。如果连续N天没有INSERT和UPDATE说明这个分区已经进入只读状态可以降级了。Oracle里可以通过视图观察段的活动情况也可以通过审计或统计信息来辅助。业务口径法有些表不能用统一时间一刀切。比如订单状态是“已完成”且超过某时间才能归档比如日志表按“保留90天”来清理。这种规则通常要写进配置表由DBA根据业务变化调整。说实话时间阈值法在流水类、日志类表上最可靠也最容易自动化。业务上几乎都认可“最近几个月的需要在线查询更早的能查到就行慢一点没关系”。如果你是做金融、电商、运营商这类数据的大概率也会落到这个方案上。2.2 冷热分区的物理隔离别让小池子拖累大池子判定了冷分区下一步是把冷数据从原有的高性能表空间挪出去。我强烈建议在物理上把冷热分区分开而不是只在逻辑上标记一下。原因有几层冷数据的特点是“极少写、偶尔读”完全没必要放在高性能存储上。把冷分区移动到独立表空间后可以做更激进的压缩压缩后的段大小变化不会影响热分区的空间估算。如果将来想对整个冷数据表空间做只读处理、备份策略调整或者做独立的存储分层也会方便很多。在我们的方案里通常规划两个表空间TBS_ORDER_HOT用于最近N个月的在线分区TBS_ORDER_COLD用于历史冷分区。冷表空间可以用廉价的大容量磁盘甚至在云上就是标准存储而不是性能型存储。物理移动分区最常用的命令是ALTER TABLE order_his MOVE PARTITION p202401 TABLESPACE tbs_order_cold COMPRESS FOR OLTP PARALLEL 4 UPDATE GLOBAL INDEXES;这条命令做了三件事把分区数据重写到新表空间、按OLTP压缩重写存储、同时维护全局索引。如果不带UPDATE GLOBAL INDEXES移动完分区以后全局索引会变成UNUSABLE这个坑后面细说。还有一个重要选项ONLINE关键字。从12.2开始Oracle支持在线移动分区ALTER TABLE order_his MOVE PARTITION p202401 TABLESPACE tbs_order_cold COMPRESS FOR OLTP ONLINE UPDATE GLOBAL INDEXES;加上ONLINE之后移动期间原分区上的DML不会被阻塞这对7乘24小时的生产系统非常关键。我一般只在业务低峰期跑批量冷处理作业但也会把ONLINE加上权当双保险。2.3 大批量归档时用分区交换如果冷数据归档是一次性的、且数据量特别大用MOVE PARTITION逐个搬可能太慢。这时候可以考虑ALTER TABLE EXCHANGE PARTITION。它的思路是先把冷分区和一个结构相同的空表做交换这个空表就变成了主表里的正式分区而原来主表里的那个分区则变成了独立表之后你可以对这个独立表任意处理——压缩、移动表空间、甚至直接DROP。-- 创建结构一致的目标表 CREATE TABLE order_his_ext ( ... ); -- 把主表里的 p202312 分区换出来 ALTER TABLE order_his EXCHANGE PARTITION p202312 WITH TABLE order_his_ext; -- 之后可以对新表做压缩搬迁 ALTER TABLE order_his_ext MOVE TABLESPACE tbs_order_cold COMPRESS FOR QUERY LOW;交换分区的速度取决于元数据更新而非数据搬运所以在海量数据场景下优势明显。但它要求操作者对分区结构和约束非常清楚我建议在自动化脚本里把交换作为“备选路径”常规跑批还是以MOVE PARTITION为主避免复杂度上升。3. 压缩冷分区到底该选哪一档先看懂Oracle压缩的等级再说3.1 Oracle压缩选项一张表看清压缩不是越猛越好关键是找到适合冷数据的那个档位。Oracle主流压缩方式如下压缩方式适合场景典型压缩比说明COMPRESS BASIC数据仓库批量加载、只读数据2-4倍基本压缩写路径开销小读取有解压CPU成本COMPRESS FOR OLTP在线交易混合读写2-4倍需要高级压缩选项授权压缩和解压开销可控COMPRESS FOR QUERY LOW只读、以查询为主5-10倍HCC压缩道路通常需要存储层配合COMPRESS FOR ARCHIVE HIGH归档不需频繁访问10-15倍以上HCC压缩CPU开销最大适合极冷数据注意一点COMPRESS FOR OLTP属于Oracle高级压缩选件的一部分购买许可的时候要确认一下。HCC混合列式压缩最早是Exadata存储上的特性后来在特定版本和许可组合下也能在非Exadata存储上使用但性能表现和压缩比会有差异。如果你的环境只是普通PC服务器加中小企业版稳妥的选择是普通用户存储上用OLTP压缩Exadata上再考虑QUERY LOW或ARCHIVE HIGH。3.2 为什么压缩能同时改善查询性能和存储很多刚入门的DBA有个误解认为压缩只是省空间查询反而会变慢。实际不完全对。压缩后每个Block里面装的行数变多了原本需要读取10个块才能扫完的数据压缩后可能只需要读2-3个块。I/O大幅减少这对顺序扫描和全分区扫描的查询来说性能提升非常明显尤其是冷分区经常要做历史统计数据汇总的场景。我举一个量化例子同样的订单表某个冷分区原本占用22GB用COMPRESS FOR QUERY LOW压缩后降到4.5GB。应用程序对这个分区做月度汇总压缩前需要读22GB的物理I/O跑一次约75秒压缩后只需要读4.5GB物理I/O加上解压缩的CPU开销实际耗时约22秒。查询性能提升超过3倍存储节省接近80%。代价是CPU消耗更高但冷数据查询频率低这点CPU基本可以忽略。3.3 压缩前必须想清楚的几个前提冷分区压缩不是把命令一扔就完事有几个前提条件需要先确认该分区确实是只读或极少更新的否则每次UPDATE都要解压再重压缩严重拖慢DML性能。LOB字段压缩要单独考虑。普通行压缩对LOB的压缩能力有限如果冷分区里包含大量CLOB/BLOB建议单独评估是否开启SECUREFILE压缩或者干脆延续行压缩就好。压缩是段级操作一旦对某个分区执行了压缩后续新插入的数据也会按压缩格式存储。所以不要对热分区执行压缩否则写路径开销立刻上来。统计信息收集要在压缩完成之后重新跑一遍。压缩会大幅度改变段大小、块密度和统计分布不重新收集统计信息优化器会基于压缩前的统计做错误估算。4. 把“自动”彻底落地一套可上生产的冷分区调度脚本4.1 设计思路与整体流程前面讲了原理和选型这一节给出一个可以实际部署的自动化方案。目标是每天凌晨跑一个PL/SQL批处理作业自动找出“年龄超过N个月”的分区把还没压缩、还在热表空间的冷分区自动迁移并压缩。整个流程分四步读规则从配置表读取冷却阈值比如3个月这样调整规则不用改代码。扫分区遍历目标表的所有分区解析每个分区的高值边界结合当前日期判断哪些分区达到冷却条件。迁移压缩对符合条件的分区逐个执行MOVE PARTITION带上表空间、压缩级别和索引维护参数。收尾验证结束后重新收集统计信息并检查索引状态、分区大小输出处理报告。4.2 识别冷分区的关键解析分区边界自动化的核心难点不在执行MOVE而在“准确判断哪些分区已经凉了”。Oracle的数据字典里分区的上界值存在ALL_TAB_PARTITIONS.HIGH_VALUE但它是一个长字符串类型的表达式。如果是按月范围分区我们需要把它解析成日期再比较。SELECT table_name, partition_name, high_value FROM all_tab_partitions WHERE table_name ORDER_HIS ORDER BY partition_position;HIGH_VALUE常见的形式是TO_DATE( 2024-01-01 00:00:00, SYYYY-MM-DD HH24:MI:SS, NLS_CALENDARGREGORIAN)。解析思路很简单从字符串中间抽取日期部分用TO_DATE转成DATE类型然后和阈值日期对比。如果是每个月一个分区、分区边界设置为每个月第一天那么一个分区的高值如果是2024年1月1日说明这个分区装的是2023年12月的数据。只要这个日期早于当前日期减去冷却月数就可以判定为冷分区。4.3 核心脚本示例下面给出一个可运行的核心脚本框架DECLARE v_table_name VARCHAR2(50) : ORDER_HIS; v_cold_tbs VARCHAR2(50) : TBS_ORDER_COLD; v_keep_months NUMBER : 3; v_cutoff_date DATE : ADD_MONTHS(SYSDATE, -v_keep_months); v_part_name VARCHAR2(50); v_high_val_expr LONG; v_part_date DATE; v_sql VARCHAR2(1000); BEGIN FOR p IN ( SELECT partition_name, high_value FROM user_tab_partitions WHERE table_name v_table_name ORDER BY partition_position ) LOOP -- 解析HIGH_VALUE字符串提取日期部分 v_high_val_expr : p.high_value; -- 只取形如 2024-01-01 的部分按你的分区定义调整 v_part_date : TO_DATE( SUBSTR(v_high_val_expr, INSTR(v_high_val_expr, ) 1, 10), YYYY-MM-DD ); IF v_part_date v_cutoff_date THEN BEGIN v_sql : ALTER TABLE || v_table_name || MOVE PARTITION || p.partition_name || TABLESPACE || v_cold_tbs || COMPRESS FOR OLTP || ONLINE || UPDATE GLOBAL INDEXES; EXECUTE IMMEDIATE v_sql; DBMS_OUTPUT.PUT_LINE(p.partition_name || 已迁移并压缩); EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE(p.partition_name || 处理失败: || SQLERRM); END; END IF; END LOOP; -- 重新收集统计信息 DBMS_STATS.GATHER_TABLE_STATS(OWNNAME APPS, TABNAME v_table_name); END; /这段代码做了最核心的事情但真正上生产前还需要几个改造日期解析部分不要硬编码。不同的分区边界格式不同建议把解析逻辑封装成函数或者直接用正则表达式提取。每一批不要全部处理完了才收集统计信息可以分几批执行每批结束后单独收集受影响分区的统计信息。加入失败重试和巡检日志。我习惯建一张执行日志表每次跑批都记录分区名、操作时间、耗时和状态后续排查问题会省很多力气。4.4 调度时间与并发控制调度时间我建议定在凌晨1点到3点之间避开业务小高峰和备份窗口。有些人喜欢和备份错开因为MOVE PARTITION会产生大量IO与RMAN备份挤在一起会让整个存储扛不住。并发方面如果一次要搬迁十几个分区建议用PARALLEL参数控制并行度而不是手动开多个会话来并行执行多个ALTER TABLE。并行度通常设置为2到4具体看存储能力和业务容忍度。并行度过高时大量写I/O会导致磁盘队列堆积反而拖慢整体进度。5. 实测记录312GB订单表压缩到146GB历史统计查询提速两倍以上5.1 环境背景这套方案我实际在一套OLTP系统上跑过。表结构是订单流水表ORDER_HIS按月份INTERVAL分区一共61个分区5年的数据。部署前表总大小312GB其中最近3个月的热分区约45GB其余267GB是历史冷分区。原表空间全部放在同一套高性能存储上每月新增数据约15GB。实施前我对历史67个分区逐个执行了冷区判定符合冷却条件的是58个分区总计约267GB。执行策略是每个批次处理8个分区间隔10分钟全程大约2小时完成没有对业务造成任何影响。5.2 结果数据指标压缩前压缩后变化订单表总大小312GB146GB降低53%冷分区总大小267GB101GB减少约62%历史月份统计查询耗时平均75秒平均23秒提升约3倍全表扫描式宽查询耗时约310秒约155秒提升近1倍全局索引大小38GB22GB降低42%存储方面最直观312GB压到146GB释放了166GB高性能存储空间。对于云上环境这部分直接换算成成本节省。查询性能上历史数据聚合查询从75秒降到23秒原因就是扫描的物理块数量大幅减少。这里有个量化参考压缩前跑一个历史分区聚合要扫描22GB数据压缩后只扫描4.5GB虽然多了解压CPU消耗但整体时间依然明显下降。5.3 压缩后查询计划的变化这里有一个值得关注的细节压缩前优化器对于某些历史月份的聚合查询倾向于选择全分区扫描因为统计信息显示分区太大、索引的选择性优势被抵消。压缩并重新收集统计信息之后分区的高值密度变化了优化器在某些场景下反而愿意走索引路径进一步缩短了响应时间。这说明压缩冷分区除了物理层面的I/O减少还会通过统计信息间接影响执行计划的质量。5.4 一点客观提醒并不是所有查询都能变快。如果业务SQL天天以不带分区条件的模糊查询为主比如WHERE cust_id ?而不是WHERE month ? AND cust_id ?那无论怎么压缩冷分区性能改善都有限——因为你根本没有利用分区裁剪。压缩解决的是“历史数据扫描成本”的问题不是“SQL写法不走分区键”的问题。所以上线这套方案之前先把业务SQL的分区裁剪率提上去这件事优先级更高。6. 这套自动化流程里我踩过的几个容易翻车的坑6.1 全局索引UNUSABLE是最大事故源最早我跑MOVE PARTITION脚本时没带UPDATE GLOBAL INDEXES早上起来发现整张表的全局索引全部失效。查询全部走全表扫描业务直接报警。原因很简单MOVE PARTITION会物理重建分区段分区数据的物理地址全部变化基于全表的全局索引自然就失效了。以后写脚本我养成了习惯凡是涉及MOVE、TRUNCATE、EXCHANGE分区的DDL一律显式带上UPDATE GLOBAL INDEXES。虽然这会增加操作耗时全局索引需要同步更新但换来的是一夜安稳值。6.2 把分区压缩到同一个表空间等于白压还有一个容易踩的坑写MOVE PARTITION时忘了指定TABLESPACE或者指定的TABLESPACE就是原表空间。这时候压缩确实生效了段变小了但表空间里的高水位空间并没有归还回来。最终效果是表空间看起来空闲了一大截但你却无法利用这些空间因为段的新旧版本混在同一个表空间里。正确做法是像前面说的单独规划一个冷表空间。如果条件不允许新建表空间至少也要用MOVE PARTITION ... TABLESPACE 同一个表空间配合后续的SHRINK SPACE或表空间级重组但那样复杂度就上去了。能新建冷表空间直接新建省心。6.3 调度作业没考虑归档日志空间MOVE PARTITION是重操作它会产生大量的redo日志除非你在NOLOGGING模式下操作。有一段时间我把压缩脚本设到凌晨跑结果凌晨的归档日志量比平时暴增好几倍差点把归档目录撑爆。尤其是大分区建议在确认业务允许的前提下给移动操作加NOLOGGINGALTER TABLE order_his MOVE PARTITION p202401 TABLESPACE tbs_order_cold COMPRESS FOR OLTP NOLOGGING UPDATE GLOBAL INDEXES;NOLOGGING模式下产生的redo几乎为零归档日志压力骤降。代价是如果操作中途崩溃该分区需要重做所以这个参数要结合你对系统可用性的容忍度来用。6.4 别把冷热阈值写死在代码里最后一个坑是设计层面的业务变化比你想象的快。当初把“3个月”写死在PL/SQL脚本里半年后业务方说历史数据要保留6个月在线查询。改脚本本身不难但你要是把类似阈值散落在十几个存储过程里每次业务调整都要一个个翻代码很容易漏改。最好建一张配置表CREATE TABLE data_lifecycle_rule ( table_name VARCHAR2(50), keep_months NUMBER, cold_tablespace VARCHAR2(50), compress_type VARCHAR2(20), enabled VARCHAR2(1) DEFAULT Y );跑批作业启动时先读这张配置表所有规则参数都从配置里取。这样业务方提需求DBA只需UPDATE一行配置不需要重编译任何存储过程。6.5 压缩后的统计信息别忘收最后再次强调MOVE COMPRESS之后不重新收集统计信息优化器还会拿着压缩前的统计值做判断。特别是有全局索引的场景统计信息不更新可能导致执行计划严重偏离真实成本。把这个逻辑直接写进自动化收尾步骤里一天都别拖。我自己现在收到一个分区就在同一个PL/SQL块里紧接着调用DBMS_STATS.GATHER_TABLE_STATS并指定分区名不让统计信息滞后一个批次。7. 可以继续扩展的方向当前这套方案解决的是“按时间冷热分离 自动压缩”的问题架构上还留了几个可以继续深化的口子。比如冷分区的访问频率监控。现在用的是时间阈值法本质上是按业务经验来预测冷热。如果你有查询审计、AWR或者统一审计日志的数据完全可以细化成“连续N天无任何查询的分区才转冷”让系统的判断更贴近真实访问模式。再比如存储分层。冷表空间建好之后你可以结合操作系统层或者云平台的分层存储能力把冷表空间数据页自动迁移到归档存储。不用改数据库结构存储系统已经帮你完成了最后一公里的降级。还有分区交换场景下的索引处理。如果你在生产中用了EXCHANGE PARTITION做归档需要想清楚交换出去的独立表上的索引如何处理以及下一次交换回来时约束和索引是否都对得上。这些细节在自动化流程里都要提前设计而不是等出了问题再救火。总的来说自动分区管的是数据的“生长”冷分区和压缩管的才是数据的“沉淀”。两者配合起来一张表无论跑多少年在线数据量都能保持在一个稳定的区间内查询性能不会因为历史包袱逐步劣化存储成本也被控制住了。这套机制的收益会随着表的数据量增长而越来越明显越早落地越划算。