Oracle 12c分区表新特性实战：异步索引与在线移动分区

凌晨两点被电话叫醒通常没什么好事。那次是Oracle Database里一张按天分区的流水表接近1TB批处理例行TRUNCATE三十天前的分区脚本里漏了UPDATE GLOBAL INDEXES十几分钟后业务SQL大量报ORA-01502两千多人同时用的系统跟着抖了半个多小时。这个场景在11g时代几乎是分区维护的必修课也是每一个分区表DBA最怕遇到的“常规事故”。12c推出后分区表在索引维护、在线移动、冷热分层这些方向上都改了很多玩法这个系列的第二篇我就把几个对生产最有影响的新特性挨个拆开讲。没看过第一篇也不影响阅读每个特性我都会从原理说到实操再把生产环境中踩过的坑一并交代清楚。1. 先看一个真实的生产故障TRUNCATE分区后全局索引集体失效1.1 传统分区维护的“原罪”目录作废式的全局索引要理解12c的改动先得明白11g及更早版本为什么一TRUNCATE分区就索引失效。分区表的全局索引是一棵B树索引索引条目里保存着键值和对应的ROWID。ROWID本身包含了数据所在的相对文件号、块号和行号。当你TRUNCATE掉一个分区Oracle实际上把这个分区对应的整个段都删了数据块从物理上消失了。此时如果全局索引中还保留着这些ROWID索引就变成了指向空块的“坏目录”。Oracle的处理原则非常保守一个分区被物理移除后原来记在这个分区上的所有索引条目要么被同步删除要么整体作废。同步删除的代价很大因为要在B树索引中逐个定位并删除大量叶子块条目TRUNCATE原本“秒级”的优势就没了。因此Oracle默认选择了“整体作废”——把全局索引标记为UNUSABLE。应用再查这个索引直接报ORA-01502。打个比方一本几百页的小说中间一章被整章撕掉了。目录如果不改读者按目录翻到那一页看到的是白纸。图书编辑为了避免读者翻到白纸最省事的方式是宣布整本目录全部作废重新排版。可重排目录的成本高得惊人。分区表的全局索引就是这本目录TRUNCATE分区就是撕掉一章。11g时代的标准解法是写操作时带上UPDATE GLOBAL INDEXES让Oracle在TRUNCATE的同时同步维护索引。这个选项好用但会产生大量的索引块更新动作在几千万行的分区上一个TRUNCATE从“秒杀”变成“分钟级”而且期间所有相关全局索引都会被锁住。所以很多脚本图省事不写事故就这么发生了。1.2 12c的异步全局索引维护先把坑留着后台再填12c引入了一个叫“异步全局索引维护”的机制直接改掉了上面的默认逻辑。现在你执行ALTER TABLE sales TRUNCATE PARTITION p_2024_09;就算不带UPDATE GLOBAL INDEXES这个分区表上的全局索引也不会变成UNUSABLE。Oracle把原本“要么全删、要么作废”的索引维护拆成了两个阶段。第一阶段只做标记。TRUNCATE发生时Oracle在数据字典里记录下这个分区涉及到的索引范围标记成“待清理”的孤儿条目。这个阶段不需要逐条去碰B树所以TRUNCATE依然是秒级完成。第二阶段后台异步清理。Oracle会在后续的某个时间点由内部进程按照记录的待清理范围慢慢删除这些孤儿索引条目。清理过程对应用透明应用查询时索引依然可用只是性能上可能会有短暂波动。怎么确认当前索引有没有孤儿条目12c的索引视图里新增了一个字段ORPHANED_ENTRIESSELECT index_name, status, orphaned_entries FROM all_indexes WHERE table_name SALES;正常情况下你会看到STATUS是VALIDORPHANED_ENTRIES是YES。过一段时间等后台清理完成后再查ORPHANED_ENTRIES会变成NO。如果长期是YES说明后台清理还没来得及跑或者系统负载一直很高把它“饿”着了。新旧行为对比可以从这张表看得很清楚场景11g及更早12cTRUNCATE分区后全局索引变为UNUSABLE必须重建保持VALID后台异步清理TRUNCATE分区本身耗时不加UPDATE INDEXES时秒级秒级且无需担心索引失效对应用影响大量ORA-01502业务中断查询照常走索引偶发性能抖动运维干预必须重建或同步维护监控ORPHANED_ENTRIES必要时REBUILD1.3 实测一次不写UPDATE INDEXES的TRUNCATE分区我找一个测试库给你演示一遍完整链路。先建一张分区表并创建全局索引CREATE TABLE t_part ( id NUMBER, c1 VARCHAR2(100), created_date DATE ) PARTITION BY RANGE (created_date) ( PARTITION p_2024_01 VALUES LESS THAN (DATE 2024-02-01), PARTITION p_2024_02 VALUES LESS THAN (DATE 2024-03-01), PARTITION p_2024_03 VALUES LESS THAN (DATE 2024-04-01) ); INSERT INTO t_part SELECT rownum, x, DATE 2024-01-15 FROM dual CONNECT BY LEVEL 300000; COMMIT; CREATE INDEX idx_t_part_id ON t_part(id);然后执行TRUNCATE注意这里不加UPDATE GLOBAL INDEXESALTER TABLE t_part TRUNCATE PARTITION p_2024_01;如果这是11g下一步大概率就是ORA-01502。但12c上你直接去查索引状态SELECT index_name, status, orphaned_entries FROM all_indexes WHERE table_name T_PART;结果会显示该全局索引仍为VALID且ORPHANED_ENTRIESYES。也就是说业务可以不中断查询照样走索引后台异步清理随后会把这些孤儿条目处理掉。如果你等不及后台或者批次分区特别多想强制清理最可靠的方法仍然是重建这个全局索引。执行REBUILD之后ORPHANED_ENTRIES会变为NO。注意不要频繁做全局索引REBUILD那个成本也不低最好挑业务低峰期。1.4 这里的坑异步不等于没有代价我在实际生产里观察到的几个问题值得提前说清楚。第一TRUNCATE之后如果应用立刻拿索引做高频点查短时间内可能出现偶发的“慢查询”。原因是索引叶子块里还有一些待删除的孤儿条目扫描路径变长CPU和IO都会略涨。遇到那种“TRUNCATE完马上就是业务高峰”的场景建议还是老老实实加UPDATE GLOBAL INDEXES同步维护别贪异步的秒级。第二大批量TRUNCATE分区之后如果系统负载一直很高后台清理可能一直挤不进调度孤儿条目会积压。我在一套生产库里就见过积压到几百万个孤儿条目、索引段明显膨胀的情况。解决办法是低峰期做个全局索引REBUILD顺便把段收缩回来。第三还有一类老库从11g升级到12c之后应用仍然写死“TRUNCATE前先DROP全局索引完成后重建”的老脚本。没必要。12c完全可以不加UPDATE INDEXES让Oracle异步处理前提是你的运维团队理解这个机制。12c对TRUNCATE/DROP分区的这个改变相当于把“要么全删、要么作废”的两难选择变成了“先记账、后慢慢还”的弹性方案。它解决的是分区维护里最痛的一环。2. 在线移动分区表压缩和数据搬迁不用再开窗口2.1 为什么生产环境会频繁MOVE分区分区表用久了经常要做分区移动。最多见的需求有三个一是历史分区想启用表压缩把几年前的冷数据从几十GB压到几GB二是分区所在的表空间想整合或者要迁到新存储三是分区段碎片严重想重组一下。11g里MOVE PARTITION命令本身不复杂ALTER TABLE sales MOVE PARTITION p_2022 TABLESPACE ts_archive COMPRESS;但这条命令一旦跑起来老DBA心里就开始打鼓。MOVE分区期间该分区的数据会被复制到新段原分区上的所有DML会被阻塞如果忽略索引维护移动完全局索引大概率变成UNUSABLE即便加了UPDATE GLOBAL INDEXES大分区移动的窗口期也往往需要几十分钟到几小时。对7x24的在线系统来说这么长的窗口很难批。12c给MOVE PARTITION增加了一个ONLINE关键字把整个操作变成了在线版本。2.2 一条ONLINE让MOVE从停机变无感语法上就是在原有的MOVE PARTITION后面加上ONLINE。比如我想把一个2022年的分区移动到归档表空间并启用压缩ALTER TABLE sales MOVE PARTITION p_2022 TABLESPACE ts_archive COMPRESS FOR OLTP ONLINE UPDATE INDEXES;执行过程中应用对p_2022分区的SELECT、INSERT、UPDATE、DELETE都不会被阻塞。以前需要停业务批个维护窗口的活现在可以挑白天做。这里有两个选项要理解清楚。UPDATE INDEXES告诉Oracle在分区移动完成后同步维护所有相关索引。没有它即使MOVE是ONLINE的移动完成后全局索引仍有可能失效本地索引也可能需要重建。所以生产环境我建议总是加上。TABLESPACE和COMPRESS这两个是移动分区的核心目的。TABLESPACE决定新段落在哪个表空间COMPRESS FOR OLTP是行压缩适合OLTP场景的历史分区。如果你想要更高的压缩率可以使用COMPRESS FOR ARCHIVE HIGH这类列压缩但受版本和压缩选件限制并不是所有环境都能用上线前要确认许可和底层存储是否支持。2.3 底层的“复制切换”逻辑在线MOVE分区能做到无感底层思路和Oracle在线重定义DBMS_REDEFINITION类似。执行时Oracle先生成一个临时段把分区数据复制到临时段复制期间应用对原分区的DML变化会被记录到内部的增量变更记录里。数据复制完成后会有一个很短的“切换”动作在分区上做一次短暂的排他锁把复制期间的增量变更应用上去然后将临时段和原段交换最后删除旧段。对绝大多数业务来说这个切换瞬间极短感受就是“没有锁”。所以执行前必须给临时段预留足够的空间。临时段的大小基本等于要移动的分区大小。如果原分区有200GB目标表空间和临时表空间至少各得留出200GB的余量否则挪到一半直接报空间不足收尾很麻烦。另外复制期间如果有大量DML涌入增量变更记录的膨胀可能非常快。我在生产上跑过一次200GB分区移动晚上8点启动期间有一个批量任务一直在更新该分区等到第二天早上去看分区移动还没结束临时段已经涨到了接近300GB。后来我把该分区的写入任务停了才顺利完成。2.4 选择和限制Online Move不是万能药ONLINE MOVE PARTITION适合在线压缩和跨表空间搬迁但它不是没有前提。一是对特殊列类型的限制。如果分区表里包含LONG、用户自定义类型等特殊列ONLINE方式可能不支持只能退回传统MOVE这类表建议先用DBMS_REDEFINITION跑通再上生产。二是执行期间DDL会被阻塞。DML不锁但ALTER、DROP这些DDL会被挡住。如果你的运维操作和定期DDL调度有冲突需要提前排好节奏。三是切换瞬间如果和某条长事务撞在一起可能产生轻微等待。绝大多数应用感知不到但对于那种对锁特别敏感的交易系统我还是建议放在业务低峰期执行别非挑全天最高峰。四是不要把它理解成“在线就能无限并发写”。它解决的是“不用停业务”的问题不代表MOVE过程中对分区的写操作完全免费。3. 部分索引给热分区建索引给冷分区省钱省空间3.1 冷热分区共享同一套索引的老问题很多公司的大表都按时间范围分区最近三个月是热数据往前几年的分区基本没人查。可问题是只要表上有一个普通索引不管是全局还是本地索引结构和数据一样跨越所有分区。三年前的冷分区一年可能只被摸一次它的索引段却每天都在占用空间每次对这张表做DML时还都要同步维护。我帮客户诊断过一张分区表表本身30GB但因为历史分区上带着三个大索引整张表的存储占用到了65GB。冷分区占比大概70%也就是至少有二十多GB的索引空间是闲置的。12c的部分索引就是来解决这个问题的。它允许你在同一张分区表上只对一部分分区创建索引另外一部分分区不建索引。设计理念和“冷热分层存储”一致只是把分层做到了索引层面。3.2 创建部分索引先给分区打INDEXING开关实现分三步。第一步在分区定义里把冷分区标记为INDEXING OFF。例如新表按日期分区早于2022年的分区不参与索引CREATE TABLE orders ( order_id NUMBER, order_date DATE, status VARCHAR2(20) ) PARTITION BY RANGE (order_date) ( PARTITION p_2021 VALUES LESS THAN (DATE 2022-01-01) INDEXING OFF, PARTITION p_2022 VALUES LESS THAN (DATE 2023-01-01) INDEXING ON, PARTITION p_2023 VALUES LESS THAN (DATE 2024-01-01) INDEXING ON );注意INDEXING ON是默认值实时热分区可以省略不写。已经有表也没关系可以用ALTER TABLE修改分区的索引属性ALTER TABLE orders MODIFY PARTITION p_2021 INDEXING OFF;第二步创建本地部分索引语法是在LOCAL后面加PARTIALCREATE INDEX idx_orders_status ON orders(status) LOCAL PARTIAL;这样p_2022和p_2023分区会有索引分区p_2021分区没有索引段。第三步验证一下。查USER_IND_PARTITIONS你会发现p_2021上根本不存在这个索引的索引分区SELECT partition_name, status FROM user_ind_partitions WHERE index_name IDX_ORDERS_STATUS ORDER BY partition_name;这种方法对全局索引也适用。如果你在order_id上建一个全局索引希望冷分区不参与可以写CREATE INDEX idx_orders_id ON orders(order_id) GLOBAL PARTIAL;全局部分索引的执行逻辑是只覆盖INDEXING ON的分区优化器优化查询时会考虑这个覆盖范围。3.3 什么场景收益最大最典型的是订单系统。订单表保存五年数据最近一年在线交易频繁查询更早的历史分区只是合规保留。我们就可以让热分区带着本地索引冷分区不带索引。再配合只读分区效果翻倍历史分区不允许DML也就不存在索引维护成本同时因为没有索引空间占用大幅下降。数据从热分区向冷分区滚动时流程要做的事情就是把分区从INDEXING ON改成INDEXING OFF然后用在线的MOVE PARTITION搬到归档表空间。整个过程不影响业务。对于批量加载冷数据的场景部分索引也能明显提速。冷分区没有索引导入几十GB数据时插入路径不会每行都去碰索引加载时间可以缩短不少。适合与不适合的场景放在一起看更直观场景是否适合部分索引原因历史分区几乎不查询适合省空间少维护批量导入冷数据适合少更新索引加载更快主键/唯一约束不适合唯一性必须全分区覆盖核心查询会跨分区查老数据需要评估冷分区无索引可能全分区扫描冷分区还会频繁DML不适合失去了索引写入效率反而影响3.4 这些情况下千万别用部分索引第一唯一约束和主键不能是部分索引。唯一性校验要求索引覆盖全部数据如果部分分区没有唯一索引就无法保证唯一性Oracle也不允许你把唯一约束建在部分索引上。所以主键索引老老实实全分区建。第二在线核心查询如果经常不带分区条件部分索引可能帮倒忙。比如前面那个orders表如果有一条高频SQL要查两年前的某笔订单而老分区没有本地索引优化器只能走分区全扫描执行计划和以前完全不同性能可能劣化。上线前一定要把SQL清单拉出来逐个确认过滤条件落在哪些分区上。第三从INDEXING OFF改回INDEXING ON并不是免费的。补索引段的时候要重建相关索引分区同样需要空间和时间。所以分区的冷热定义要想清楚别今天OFF明天ON来回折腾。4. 只读分区与引用分区增强冷数据治理的难啃骨头4.1 只读分区比权限管控更底层的防篡改12c还有一个看似不起眼、其实特别好用的功能单个分区可以设成只读。语法非常简单ALTER TABLE orders MODIFY PARTITION p_2021 READ ONLY;设置之后任何业务对p_2021分区的INSERT、UPDATE、DELETE都会被直接拒绝报ORA-14466这类只读分区错误。要恢复写入执行ALTER TABLE orders MODIFY PARTITION p_2021 READ WRITE;只读分区的好处在于它比应用层的权限控制更底层核心库维护人员即使有表的DML权限也改不动只读分区里的数据。比触发器也可靠不怕谁把触发器禁掉。归档数据要防篡改的时候这个功能是最干净的方案。需要注意两点。一是只读分区上不能直接做TRUNCATE、DROP等修改操作需要先切回READ WRITE再操作。二是如果表上的全局索引会在分区只读期间发生更新比如你MOVE了其它热分区且带了UPDATE INDEXES全局索引本身的工作不受影响但如果你打算对该只读分区做结构变更记得先把分区切回读写。4.2 引用分区表的ON UPDATE CASCADE数据迁移更省心引用分区从11g就有了它把子表按照父表的外键关系自动分区对一对多的主外键场景非常好用。12c给它补上了一个重要增强外键约束支持ON UPDATE CASCADE。以前有个尴尬的场景订单表和订单明细表是引用分区关系如果业务上不得已要修改订单主表的主键值比如两个客户归档合并子表引用分区很难自动跟着搬。12c之后可以在外键约束上声明ON UPDATE CASCADECREATE TABLE orders ( order_id NUMBER PRIMARY KEY, order_date DATE, customer_id NUMBER ) PARTITION BY RANGE (order_date) ( PARTITION p_2023 VALUES LESS THAN (DATE 2024-01-01), PARTITION p_2024 VALUES LESS THAN (DATE 2025-01-01) ); CREATE TABLE order_items ( item_id NUMBER PRIMARY KEY, order_id NUMBER, item_name VARCHAR2(100), CONSTRAINT fk_items_orders FOREIGN KEY (order_id) REFERENCES orders(order_id) ON UPDATE CASCADE ) PARTITION BY REFERENCE (fk_items_orders);此时如果合法地UPDATE父表orders的order_id子表order_items中对应的行会自动迁移到引用分区对应的位置不再需要手工搬运。不要因为有了这个特性就随意改主键。主键本质上是业务关系的锚点生产环境动不动改主键是灾难。但这个特性在“订正历史数据”“合并客户主数据”这类合法场景里非常值钱省掉一把把写UPDATE的烦恼。4.3 如果你已经用到了12.2还有几个新特性可以期待本文主要讲12.1但如果你用的是12.2或更高版本分区表还有几个更丝滑的特性值得关注。外部表分区允许你像操作普通分区表一样对外部文件按范围、列表、哈希等策略做分区。大数据平台的数据落到文件系统上数据库层面可以直接按日期切割适合做数仓贴源层。自动列表分区解决了列表分区最烦人的“新值必须先手工加分区”问题。插入一个新地区代码Oracle会自动生成新分区不用提前规划分区边界。多列列表分区让列表分区的键从单列扩展为多列分区策略的表达能力更强。这些特性细节不少这个系列后续我会单独展开。5. 我生产环境落地这些特性时的一点经验5.1 别单个上按“分区生命周期”串起来用这几个新特性单独看各自解决一个问题合在一起才是12c分区表最强的形态。我落地过一套订单分区治理方案大致是这样新数据进入当前热分区热分区保持INDEXING ON和本地索引支持高频交易查询每月月底用ONLINE MOVE PARTITION把超过六个月的冷分区挪到归档表空间并在移动时用COMPRESS压缩挪完立刻把分区改成READ ONLY彻底防篡改历史分区按策略设置INDEXING OFF后续新索引不再覆盖老分区维护窗口里TRUNCATE一个月前的中间表分区时不再写UPDATE GLOBAL INDEXES靠异步全局索引清理收尾。这套组合下来在线系统分区维护的停机时间几乎归零冷数据空间压缩了三分之二索引段规模也小了40%以上。关键不是某一个特性有多神而是它们刚好覆盖了数据从热到冷的完整生命周期。5.2 三个踩过的坑提前帮你排掉第一个坑是部分索引上线后执行计划大改。某条核心SQL原来走本地索引冷分区INDEXING OFF后老数据分区的查询直接变成全分区扫描响应时间从几百毫秒变四五秒。后来我们调整了SQL强制热分区走索引冷分区查询也接受了扫描现实响应时间才算恢复正常。所以上线前务必跑一轮全量SQL。第二个坑是ONLINE MOVE PARTITION的临时段爆盘。一次60GB分区移动归档表空间只剩50GB我以为够用结果临时段和复制段叠加差点把表空间塞满。从此我给自己立了个规矩执行前先检查目标表空间和临时表空间剩余空间都要大于分区大小的1.5倍才动手。第三个坑是异步全局索引清理在极端高并发下仍有IO抖动。不要以为12c TRUNCATE分区就完全无感了孤儿条目积压多的时候索引扫描路径确实会变长。碰上核心大表我反而建议在脚本里显式加UPDATE GLOBAL INDEXES把索引维护成本放在可控的批处理窗口里。5.3 许可和版本提醒最后提醒一句版本和许可的事。分区功能本身在Oracle Enterprise Edition里属于分区选件12c的异步索引、部分索引等高级特性基本都要企业版才支持。压缩相关的高级列压缩还需要额外的压缩选件。如果你的环境是标准版或者SE2先确认许可别等架构都设计好了才发现功能不可用。版本上12.1.0.1和12.1.0.2对部分特性的成熟度也有差别能用12.1.0.2或更高版本就不要守着12.1.0.1。生产环境升级前把本文涉及的特性在测试库完整验证一遍再谈上线。上面这些内容是我在项目交付和故障处理中一步步试出来的希望能帮你少走弯路。这个系列下一篇我会接着拆12.2/19c里分区表的自动列表和外部表分区到时候见。