Oracle 分区表治理实战：从全局索引失效到裁剪生效与在线重定义

dbaai

一、具体的问题

一张交易流水表，2.3 亿行，按交易日期做了 interval 分区，每天一个分区，大概 400 个分区。当初分区的理由是"查询都带日期，分区裁剪以后只扫一片，肯定快"。上线半年后的实际情况是：

• 业务按单日查明细，P99 还是 1.8 秒，和分区前几乎没差别；
  
• 月初跑汇总，扫的行数比预期多两个数量级，AWR 里 PX 相关等待突然起来了；
  
• 运维脚本每天凌晨 DROP PARTITION 删过期分区，删完第二天报表大面积报 ORA-01502: 索引或这类索引的分区处于不可用状态；
  
• 有几次 DROP 之后，原本 3 秒的报表变成 40 秒，重建索引又要 20 分钟窗口。
  

四个现象看着分散，其实是同一件事的四个面：分区建了，但裁剪没生效；索引建了，但局部性没设计对。分区不是"建完就快"的开关，它只在 Query Optimizer 能在解析阶段把分区列表收窄成很小的一片时才有价值。

二、核心原理

先把三件事分清楚，这是后面所有操作的地基。

第一，裁剪发生在解析期，不是执行期。 分区裁剪本质是优化器在生成执行计划时，把 PARTITION RANGE 算子后面的分区范围算出来，写进计划的 Pstart / Pstop。这意味着：如果过滤条件里的分区键值要等绑定变量代入才知道，优化器只能给出"全分区"的粗计划（Pstart=1，Pstop=400），真正的裁剪交给运行时的 PARTITION RANGE ITERATOR 逐分区试探。游标一旦被共享复用，第一次算出来的裁剪结果就可能被后续不同参数沿用，这就是"有时候快有时候慢"的来源。

第二，任何包裹分区键的表达式，都会让裁剪在解析期彻底失效。 TO_CHAR(trade_date,'YYYY-MM-DD') = '2026-10-01'、TRUNC(trade_date) = ...、trade_date + 0 = ...，优化器没法反推区间，只能退化成全表（全分区）扫描。隐式类型转换也是同一个后果：分区键是 DATE，条件写 '2026-10-01'，字面量按会话的 NLS_DATE_FORMAT 解释，语义上还算能裁，但只要格式串不一致就会抛 ORA-01861；写成 TO_DATE 显式转换、或者用 ANSI 字面量 DATE '2026-10-01'，才是可控的。

第三，索引的 LOCAL / GLOBAL 决定了维护代价落在谁头上。 本地索引（LOCAL）每个分区一份索引段，DROP PARTITION 时只连带删掉对应那一份，其他分区索引不受影响，代价近似 O(1)。全局索引（GLOBAL）是一整棵 B 树，跨所有分区，DROP PARTITION 会让这棵树上被删掉的那部分条目失去指向，整棵索引直接标记 UNUSABLE —— 除非加 UPDATE GLOBAL INDEXES，或者先 UNUSABLE 再重建。反过来，本地索引在"查询不带分区键"时会退化成扫全部 400 个索引分区（PARTITION RANGE ALL + 每个分区一次 INDEX RANGE SCAN），索引扫描被放大 400 倍，这时候全局索引反而更快。

所以设计上的判断标准很朴素：查询几乎都带分区键 → 本地索引；有少量关键查询不带分区键 → 那几条查询单独建全局索引，并接受 DROP 时维护它；两者都要 → 用全局分区索引，按分区键做前导列。

三、实例参考（动手步骤）

步骤 0：确认现状基线

先别改任何东西，把三张视图查清楚：有多少分区、索引是本地还是全局、当前计划到底裁没裁。

2. -- 分区规模与边界
3. SELECT COUNT(*) part_cnt,
4.        MIN(partition_position) min_pos,
5.        MAX(partition_position) max_pos
6. FROM   dba_tab_partitions
7. WHERE  table_owner='APP' AND table_name='T_TRADE';
9. -- 索引局部性：LOCALITY 是 LOCAL / GLOBAL，PARTITIONED 是 YES / NO
10. SELECT index_name, locality, partitioned, status
11. FROM   dba_part_indexes
12. WHERE  table_owner='APP' AND table_name='T_TRADE';
14. -- 有没有已经挂掉的索引分区
15. SELECT index_name, partition_name, status
16. FROM   dba_ind_partitions
17. WHERE  index_owner='APP' AND status <> 'USABLE';

复制代码

步骤 1：把"没裁剪"钉死

只用执行计划里的两列判断，不看耗时，耗时会被缓存和并发干扰。

2. EXPLAIN PLAN FOR
3. SELECT COUNT(*), SUM(amount)
4. FROM   app.t_trade
5. WHERE  TO_CHAR(trade_date,'YYYY-MM-DD') = '2026-10-01';
7. SELECT * FROM TABLE(dbms_xplan.display(NULL,NULL,'BASIC +PARTITION +COST'));

复制代码

看输出里的 Pstart / Pstop：

• Pstart=1, Pstop=400，配合 PARTITION RANGE ALL → 裁剪完全失效；
  
• Pstart=287, Pstop=287，配合 PARTITION RANGE SINGLE → 裁剪到单分区，正常；
  
• Pstart=KEY, Pstop=KEY，配合 PARTITION RANGE ITERATOR → 运行时才裁，属于绑定变量场景的次优状态。
  

改写成不带函数的条件，再比对一次：

2. EXPLAIN PLAN FOR
3. SELECT COUNT(*), SUM(amount)
4. FROM   app.t_trade
5. WHERE  trade_date >= DATE '2026-10-01'
6. AND    trade_date <  DATE '2026-10-02';

复制代码

这一改，Pstop 通常立刻从 400 掉到单值。这一步不需要改任何对象，是纯粹白捡的收益，先做。

步骤 2：给维护脚本加保护

DROP PARTITION 的三种写法，代价完全不同，选哪个取决于索引设计：

2. -- 写法 A：全局索引异步维护，DROP 立即返回，索引保持 USABLE
3. ALTER TABLE app.t_trade DROP PARTITION p20250930 UPDATE GLOBAL INDEXES;
5. -- 写法 B：先让全局索引失效，DROP 后再统一重建（窗口内该索引不可用）
6. ALTER TABLE app.t_trade DROP PARTITION p20250930 UPDATE GLOBAL INDEXES;
7. -- 若仍失效，逐个重建：
8. ALTER INDEX app.idx_trade_cust REBUILD ONLINE PARTITION;
10. -- 写法 C：索引已全部 LOCAL，直接 DROP，无额外代价
11. ALTER TABLE app.t_trade DROP PARTITION p20250930;

复制代码

注意写法 A 里的 UPDATE GLOBAL INDEXES 在 12c 之后是异步清理（会留一条后台 SYS_JOB），DROP 立刻返回但索引里可能残留孤儿条目，需要后续 ALTER INDEX ... COALESCE 或 REBUILD 收尾。别以为加了这句就万事大吉。

步骤 3：把非分区键上的全局索引改成本地

以客户号索引为例。判断依据是这条索引支撑的查询有没有带日期。带 → 改本地；不带 → 保留全局。

2. -- 建本地索引（ONLINE 不阻塞 DML），再删旧索引，避免空窗
3. CREATE INDEX app.idx_trade_cust_l ON app.t_trade(customer_id)
4.   LOCAL ONLINE COMPUTE STATISTICS;
6. -- 校验新旧索引的选择性没有实质差异后再删
7. DROP INDEX app.idx_trade_cust ONLINE;

复制代码

改完之后，步骤 2 的写法 C 就能用了，凌晨维护窗口从 20 分钟压到几秒。

步骤 4：普通表转分区表，不锁业务

如果表当初没分区、现在想补，别用 ALTER TABLE ... MODIFY PARTITION BY（会锁表）。用在线重定义：

2. -- 1) 建同结构的空分区表
3. CREATE TABLE app.t_trade_int (
4.   trade_id NUMBER, trade_date DATE, customer_id NUMBER, amount NUMBER(18,2)
5. ) PARTITION BY RANGE (trade_date) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
6.   (PARTITION p_init VALUES LESS THAN (DATE '2026-01-01'));
8. -- 2) 校验可重定义
9. BEGIN
10.   dbms_redefinition.can_redef_table('APP','T_TRADE',
11.     dbms_redefinition.cons_use_pk);
12. END;
13. /
15. -- 3) 启动 → 同步增量 → 切换（中间业务可正常读写）
16. BEGIN
17.   dbms_redefinition.start_redef_table('APP','T_TRADE','T_TRADE_INT');
18.   dbms_redefinition.sync_interim_table('APP','T_TRADE','T_TRADE_INT');
19.   dbms_redefinition.finish_redef_table('APP','T_TRADE','T_TRADE_INT');
20. END;
21. /

复制代码

finish 那一瞬间会拿一把短暂的排他锁（毫秒级），别放在业务高峰。

步骤 5：统计信息按分区收，别全表乱收

分区表的统计信息有两个层级：全局（GLOBAL）和分区（PARTITION）。只收全局会导致各分区选择性判断失真；每次都全收又撑不住窗口。

2. -- 只收最近变化的分区 + 全局，用增量方式
3. BEGIN
4.   dbms_stats.gather_table_stats(
5.     ownname     => 'APP',
6.     tabname     => 'T_TRADE',
7.     partname    => 'SYS_P1287',
8.     granularity => 'PARTITION',
9.     cascade     => TRUE,
10.     degree      => 4);
11. END;
12. /

复制代码

granularity => 'AUTO' 在分区表上默认会同时收 GLOBAL 和 PARTITION，代价翻倍。日常维护明确指定 'PARTITION'，全局统计放在低峰单独跑。

治理前后对比

指标	治理前	治理后
单日明细查询 P99	1820 ms	34 ms
扫描分区数（单日查询）	400（Pstart=1/Pstop=400）	1
报表扫描行数	1.7 亿	41 万
DROP PARTITION 后索引状态	UNUSABLE，需重建 20 分钟	全部 USABLE，耗时 < 1 s
凌晨维护窗口占用	25 分钟	90 秒
全局索引个数	6 个	1 个（仅无分区键查询用）
月度统计收集耗时	48 分钟	6 分钟（分区级增量）

四、实操检查清单

• [ ] 用 dba_tab_partitions 确认分区总数与最大分区位置，心里有数再动手
  
• [ ] 用 dba_part_indexes 列出全部索引的 LOCALITY，标出所有 GLOBAL 索引及其支撑的查询
  
• [ ] 对 Top 10 慢 SQL 逐条 EXPLAIN PLAN，把 Pstart/Pstop 抄进表格，作为改造前基线
  
• [ ] 全库排查分区键被函数包裹的 SQL：TO_CHAR、TRUNC、SUBSTR、+0、隐式类型转换
  
• [ ] 所有分区表的日期条件统一改成 ANSI 字面量 DATE '2026-10-01' 或显式 TO_DATE(...,'YYYY-MM-DD')
  
• [ ] DROP PARTITION 脚本统一加 UPDATE GLOBAL INDEXES，并在其后追加索引 COALESCE 收尾
  
• [ ] 无分区键的全局索引数量控制在个位数，其余全部转 LOCAL 并 ONLINE 建、ONLINE 删
  
• [ ] dba_ind_partitions 中 status<>'USABLE' 的行必须为 0，纳入每日巡检
  
• [ ] 分区表统计信息明确指定 granularity，禁止无脑 AUTO 全表收集
  
• [ ] 改造后重跑同一批 SQL，把新的 Pstart/Pstop 与基线对齐，逐条确认裁剪生效
  

几个容易踩的坑

一、以为加了分区就等于加了过滤条件。 分区只是物理上的数据分堆，优化器要不要只扫一堆，取决于 WHERE 里能不能算出区间。SQL 不改，分区白建。

二、UPDATE GLOBAL INDEXES 当成万能药。 12c 起它是异步的，DROP 返回了不代表索引干净。密集 DROP 之后不去 COALESCE，索引会越用越胖，某天查询突然变慢还找不到原因。

三、本地索引一律比全局索引好。 不带分区键的查询走本地索引，是 400 次 INDEX RANGE SCAN 的 PARTITION RANGE ALL，比一次全局索引扫描慢一个量级。索引形式要跟着查询走，不是跟着表走。

四、INTERVAL 分区自增出来的分区名是 SYS_Pxxxx。 别在脚本里硬编码 P20261001 这种名字，某天 MAXVALUE 分区被自动切开，名字就对不上了。删除过期分区请按 dba_tab_partitions.high_value 反查实际分区名。

五、dbms_redefinition 忘了 sync_interim_table 直接 finish。 中间表数据不完整，切过去就是数据丢失。步骤 3 的顺序不能省，大表还要多同步几轮。

六、分区裁剪生效了但统计信息还是旧的。 新分区刚插完数据，num_rows 是 0 或估算值，优化器会按"这个分区很小"选择嵌套循环，实际几十万行，执行计划彻底跑偏。每次大批量装载后补收该分区统计，比改 SQL 更管用。

七、把 DROP PARTITION 当成删除数据的常规手段。 它确实比 DELETE 快得多（DDL 直接改段），但没有回退余地，误删只能靠备份。生产上先 EXCHANGE PARTITION 把分区换到临时表、确认数据无误、再 DROP 临时表，多一步换一份保险。