Oracle 19c从键值对列提取值至新列的优化实现咨询

你的原SQL思路可行，但存在可优化空间，以下是具体改进建议和替代方案：

一、正则表达式优化（针对少量固定键场景） #

原语句用SUBSTR截取值的方式不够灵活，若键名长度变化就得调整截取位置，且惰性匹配.*?会增加正则引擎的回溯开销。优化后的写法可直接通过正则捕获组提取值，无需额外截取：

SELECT
  Col1,
  Col2,
  -- 提取key1对应值，无匹配则返回空
  REGEXP_SUBSTR(Col2, 'key1:([^;]+)(;|$)', 1, 1, NULL, 1) AS Col3,
  -- 提取key2对应值
  REGEXP_SUBSTR(Col2, 'key2:([^;]+)(;|$)', 1, 1, NULL, 1) AS Col4,
  -- 提取key3对应值（无匹配时自动为空）
  REGEXP_SUBSTR(Col2, 'key3:([^;]+)(;|$)', 1, 1, NULL, 1) AS Col5,
  -- 提取key4对应值
  REGEXP_SUBSTR(Col2, 'key4:([^;]+)(;|$)', 1, 1, NULL, 1) AS Col6
FROM table_name;

优化点说明： #

• 移除多余SUBSTR：通过正则捕获组([^;]+)直接匹配键值对中的值（非分号字符），避免依赖键名长度，适配不同长度的键。
• 提升正则性能：用[^;]+替代.*?，减少正则引擎的回溯操作，处理大量数据时性能更优。

二、JSON_TABLE方案（针对多键或需扩展场景） #

如果需要提取的键数量较多，或者后续有扩展需求，Oracle 19c支持的JSON_TABLE方法更易维护，且性能稳定：

SELECT
  t.Col1,
  t.Col2,
  j.key1 AS Col3,
  j.key2 AS Col4,
  j.key3 AS Col5,
  j.key4 AS Col6
FROM table_name t,
     JSON_TABLE(
       -- 将分号分隔的键值对转换为JSON格式
       '{' || REPLACE(REPLACE(t.Col2, ';', '","'), ':', '":"') || '}'
       COLUMNS(
         key1 VARCHAR2(100) PATH '$.key1',
         key2 VARCHAR2(100) PATH '$.key2',
         key3 VARCHAR2(100) PATH '$.key3',
         key4 VARCHAR2(100) PATH '$.key4'
       )
     ) j;

方案说明： #

• 通过字符串替换将key1:Val1;Key2:Val2格式转换为{"key1":"Val1","key2":"Val2"}的JSON结构。
• 利用JSON_TABLE将JSON对象拆分为列，无对应键时自动返回空值，符合需求。
• 新增键时只需在COLUMNS子句中添加对应字段，维护成本更低。

总结 #

• 若需提取的键数量少，优先选择优化后的正则方案，简洁高效。
• 若键数量多或有扩展需求，推荐JSON_TABLE方案，可维护性更强。

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