Oracle中结合REGEXP使用NULL时查询过慢的优化咨询

问题核心 #

生产环境中，通过STR IS NOT NULL筛选关联查询结果时耗时极久，但单独执行正则表达式逻辑或在测试集运行均正常。核心原因是生产数据量更大，原查询执行计划未做优化，导致大量无关数据被先关联再计算过滤，资源消耗过高。

优化方案 #

• 调整执行顺序：先过滤再关联
  原查询先关联三张表再计算STR并筛选，会处理大量无关数据。建议先在HNO_NOTE_TEXT表中用REGEXP_LIKE过滤出符合正则条件的记录，再与其他表关联，大幅减少后续关联的数据量。

• 简化正则表达式，减少回溯
  原正则存在冗余写法，可优化：

  • 将(new)+{1,10}简化为new{1,10}（原写法等价于匹配new重复1-10次，简化后逻辑一致但减少分组回溯）
  • 将[^.|,|?|$]改为[^.,?$]（字符集内无需转义特殊符号，减少正则引擎解析负担）
    优化后的正则表达式执行效率更高，尤其在处理大文本时效果明显。

• 避免在筛选条件中使用计算列
  STR是通过COALESCE+REGEXP_SUBSTR计算生成的列，直接用STR IS NOT NULL筛选会导致每条记录都需计算后再判断。可替换为在关联前用REGEXP_LIKE做预筛选，或给NOTE_TEXT创建基于正则的虚拟列并建立索引，让筛选直接利用索引。

• 检查并补充关联字段索引
  生产环境数据量大时，缺失索引会导致全表扫描：

  • 确保HNO_xxx表的pat_enc_id、note_id字段有索引
  • 确保PREV_POD表的pat_enc_id字段有索引
  • 确保HNO_NOTE_TEXT表的note_id、LINE字段有联合索引（因为筛选条件用到LINE=1）

• 更新表统计信息
  若生产环境表统计信息过时，Oracle可能选择低效的执行计划。执行以下命令更新相关表的统计信息：

  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'HNO_xxx');
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'PREV_POD');
  DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 'HNO_NOTE_TEXT');
  

• 优化后的查询示例

  WITH filtered_notes AS (
      SELECT note_id, note_text
      FROM HNO_NOTE_TEXT
      WHERE LINE = 1
        AND (REGEXP_LIKE(note_text, 'new{1,10}[^.,?$]wound{1,10}[^.,?$]evaluation', 'i')
             OR REGEXP_LIKE(note_text, 'foot{1,10}[^.,?$]wound{1,10}[^.,?$]clinic{1,10}[^.,?$]new', 'i'))
  )
  SELECT prev_pod.mrn_id, prev_pod.pat_enc_id, prev_pod.contact_date, fn.note_text,
         COALESCE(REGEXP_SUBSTR(fn.note_text, 'new{1,10}[^.,?$]wound{1,10}[^.,?$]evaluation', 1, 1, 'i'),
                  REGEXP_SUBSTR(fn.note_text, 'foot{1,10}[^.,?$]wound{1,10}[^.,?$]clinic{1,10}[^.,?$]new', 1, 1, 'i')) AS STR
  FROM HNO_xxx
  INNER JOIN PREV_POD ON HNO_xxx.pat_enc_id = prev_pod.pat_enc_id
  INNER JOIN filtered_notes fn ON HNO_xxx.note_id = fn.note_id;
  

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