Oracle遗留系统慢SQL优化请求：分隔符字段关联查询性能问题

问题背景 #

维护一套基于Oracle的遗留系统，无法修改现有数据存储结构：

• ofsd_temp表（约30万条记录）：包含varchar2类型的entity_nums字段，存储以竖线分隔的数字串，示例如下：

  |432124|
  |12678|762333|74774|
  

• ofdl表（约15万条记录）：entity_num为数字类型

当前执行的INSERT-SELECT语句返回约13万条数据，但执行时长波动极大（7分钟至3-4小时），原SQL如下：

insert into table
select *
from ofsd_temp sdtemp,
     ofdl dl
where '|' || dl.entity_num || '|' = sdtemp.entity_nums;

优化建议 #

• 修正匹配逻辑，改用字符串包含判断
  原SQL的等值判断仅能匹配单值格式的entity_nums（如|432124|），完全无法适配多值分隔串（如|12678|762333|74774|），这是导致执行计划不稳定、时长波动的核心原因之一。改用Oracle原生的INSTR或LIKE函数实现包含匹配：

  insert into table
  select *
  from ofsd_temp sdtemp,
       ofdl dl
  where INSTR(sdtemp.entity_nums, '|' || dl.entity_num || '|') > 0;
  

  或：

  insert into table
  select *
  from ofsd_temp sdtemp,
       ofdl dl
  where sdtemp.entity_nums LIKE '%|' || dl.entity_num || '|%';
  

• 创建函数索引优化字符串匹配性能
  普通索引无法支持字符串包含查询，可针对匹配逻辑创建函数索引，降低扫描开销：

  -- 针对INSTR匹配逻辑创建函数索引
  CREATE INDEX idx_ofsd_temp_instr_match ON ofsd_temp (INSTR(entity_nums, '|'));
  
  -- 或创建虚拟列+索引，适配LIKE匹配
  ALTER TABLE ofsd_temp ADD entity_nums_pattern AS ('%' || entity_nums || '%') VIRTUAL;
  CREATE INDEX idx_ofsd_temp_virtual_pattern ON ofsd_temp (entity_nums_pattern);
  

  注意：函数索引会增加表的DML操作开销，需结合业务写入频率评估。

• 强制指定表连接顺序，用小表驱动大表
  显式添加优化器提示，让数据量较小的ofdl表驱动ofsd_temp表，减少中间结果集的生成量：

  insert into table
  select /*+ LEADING(dl) */ *
  from ofsd_temp sdtemp,
       ofdl dl
  where INSTR(sdtemp.entity_nums, '|' || dl.entity_num || '|') > 0;
  

• 拆分批量插入，降低事务日志压力
  一次性插入13万条数据易引发日志刷写瓶颈，导致性能波动。可通过PL/SQL分批次插入，比如每次插入1万条：

  DECLARE
    v_batch_size NUMBER := 10000;
    v_total_rows NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_total_rows FROM (
      select *
      from ofsd_temp sdtemp,
           ofdl dl
      where INSTR(sdtemp.entity_nums, '|' || dl.entity_num || '|') > 0
    );
    
    FOR i IN 0 .. TRUNC((v_total_rows - 1)/v_batch_size) LOOP
      insert into table
      select *
      from (
        select t.*, ROWNUM rn
        from (
          select *
          from ofsd_temp sdtemp,
               ofdl dl
          where INSTR(sdtemp.entity_nums, '|' || dl.entity_num || '|') > 0
        ) t
      )
      where rn BETWEEN i*v_batch_size + 1 AND (i+1)*v_batch_size;
      COMMIT;
    END LOOP;
  END;
  /
  

• 更新表统计信息，稳定执行计划
  统计信息过时会导致Oracle优化器选择低效执行计划，引发时长波动。手动收集两张表的全量统计信息：

  EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'ofsd_temp', ESTIMATE_PERCENT => 100, CASCADE => TRUE);
  EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'ofdl', ESTIMATE_PERCENT => 100, CASCADE => TRUE);
  

• 用临时表预过滤脏数据
  提前过滤ofsd_temp中的空值、格式异常记录，减少无效扫描：

  CREATE GLOBAL TEMPORARY TABLE temp_ofsd_temp ON COMMIT PRESERVE ROWS AS
  SELECT * FROM ofsd_temp WHERE entity_nums IS NOT NULL AND entity_nums LIKE '|%|';
  
  CREATE INDEX idx_temp_entity_nums ON temp_ofsd_temp (entity_nums);
  
  insert into table
  select *
  from temp_ofsd_temp sdtemp,
       ofdl dl
  where INSTR(sdtemp.entity_nums, '|' || dl.entity_num || '|') > 0;
  

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