Oracle超大规模分区表更新遇ORA-01555错误及性能优化求助

• 动态SQL硬解析与变量绑定错误：原代码用EXECUTE IMMEDIATE拼接rec.td，未使用绑定变量，导致每次循环生成全新SQL，触发大量硬解析；同时动态SQL中直接引用PL/SQL变量rec.td，会导致变量未被正确解析，逻辑存在隐性错误。
• 重复子查询浪费资源：UPDATE语句的set子句和exists子句重复执行相同关联逻辑，每行数据需两次查询b_id_list，IO与CPU开销直接翻倍。
• 关联条件无法利用索引：substr(a.id,12)=to_char(b.id)中，substr(a.id,12)使the_table.id的索引失效，to_char(b.id)使b_id_list.id的索引失效，导致每次关联都需全表扫描b_id_list，7亿条数据循环下，该开销极其巨大。
• 全表扫描获取分区键：通过select distinct date_m from the_table获取分区值，需扫描7亿条数据生成distinct结果，这一步本身耗时极长——完全可直接从数据字典读取分区信息。
• ORA-01555根源：单个分区更新时间过长，回滚段被新事务覆盖；同时循环中频繁commit导致游标快照失效，查询分区键的游标在执行过程中因数据变化触发快照过旧错误。
• 并行提示使用不当：parallel(10)在PL/SQL循环的单条UPDATE中并行效果有限，未结合分区表特性做分区级并行优化。

1. 替换动态SQL为静态SQL，使用绑定变量
   去掉EXECUTE IMMEDIATE，直接在PL/SQL块中编写静态UPDATE语句，同时从数据字典读取分区键，避免全表扫描：

   set serveroutput on;
   begin
     dbms_output.put_line('START');
     FOR rec in (
       select to_date(replace(partition_name, 'DATE_M_', ''), 'YYYYMMDD') td
       from user_tab_partitions
       where table_name = 'THE_TABLE'
       order by td
     )
     LOOP
       dbms_output.put_line(rec.td);
       update /*+ parallel(10) */ the_table a
       set b_id = (select b.b_id from b_id_list b where substr(a.id,12) = to_char(b.id))
       where exists (select 1 from b_id_list b where substr(a.id,12) = to_char(b.id))
         and date_m = rec.td; -- 修正原代码中date字段的笔误，匹配分区字段date_m
       commit;
     END LOOP;
   END;
   /
   

2. 用MERGE语句替代UPDATE，消除重复子查询
   MERGE可一次性完成关联与更新，避免两次查询b_id_list，性能提升明显：

   set serveroutput on;
   begin
     dbms_output.put_line('START');
     FOR rec in (
       select to_date(replace(partition_name, 'DATE_M_', ''), 'YYYYMMDD') td
       from user_tab_partitions
       where table_name = 'THE_TABLE'
       order by td
     )
     LOOP
       dbms_output.put_line(rec.td);
       merge /*+ parallel(10) */ into the_table a
       using (select b.id, b.b_id from b_id_list b) b
       on (substr(a.id,12) = to_char(b.id) and a.date_m = rec.td)
       when matched then update set a.b_id = b.b_id;
       commit;
     END LOOP;
   END;
   /
   

3. 优化关联条件，创建合适索引

   • 在the_table上创建函数索引：create index idx_the_table_id_substr on the_table(substr(id,12)) parallel 10;
   • 在b_id_list上创建字符串类型索引（若b.id为数字）：create index idx_b_id_list_id_char on b_id_list(to_char(id)) parallel 10;
   • 若业务允许，建议调整数据类型，使a.id的后段与b.id类型一致（如将b.id转为字符串存储，或在the_table新增字段存储id后段值），彻底消除函数转换导致的索引失效问题。

4. 利用分区表特性，直接操作分区
   直接通过分区名更新，避免日期过滤开销：

   set serveroutput on;
   begin
     dbms_output.put_line('START');
     FOR rec in (
       select partition_name
       from user_tab_partitions
       where table_name = 'THE_TABLE'
       order by partition_position
     )
     LOOP
       dbms_output.put_line(rec.partition_name);
       merge /*+ parallel(10) */ into the_table partition(rec.partition_name) a
       using (select b.id, b.b_id from b_id_list b) b
       on (substr(a.id,12) = to_char(b.id))
       when matched then update set a.b_id = b.b_id;
       commit;
     END LOOP;
   END;
   /
   

5. 调整回滚段配置（临时应急）
   若ORA-01555仍出现，可临时增大回滚段：

   • 查看回滚段状态：select segment_name, tablespace_name, bytes from dba_rollback_segs where segment_name = '_SYSSMU9_1692468413$';
   • 扩展回滚段：alter rollback segment "_SYSSMU9_1692468413$" resize 10G;（根据实际情况调整大小）
   • 若使用自动回滚段管理，可增大undo表空间：alter tablespace undo_tbs add datafile '/path/to/undo02.dbf' size 10G autoextend on next 1G maxsize unlimited;

6. 减少commit频率（可选）
   频繁commit会增加事务开销，可每处理N个分区后commit一次，平衡回滚段占用与事务开销。

内容的提问来源于stack exchange，提问作者M_Gh