Oracle中用递归查询替换公式 优化大表计算性能问询

完全可行，递归查询可以实现批量替换公式中的SI*/VI*编码为实际数值，替代游标逐行处理的逻辑，大幅降低3200万+数据量下的处理耗时。

核心实现思路 #

• 聚合单元数据：将UnitData按UnitNumber和CALC_CD分组，把每个单元对应的GRP_CD与GRP_AMT映射为键值对，方便后续替换。
• 递归替换公式变量：通过递归查询遍历公式中的所有GRP_CD变量，批量替换为对应的数值，生成可直接计算的表达式。
• 链式计算公式结果：按Formula1→Formula2→Formula3→Formula4的顺序，利用前序公式的结果计算后续公式，最终得到最终结果。
• 批量插入结果：将所有计算结果批量写入Calc_Result表，减少IO交互开销。

具体SQL实现示例 #

1. 递归替换公式中的GRP_CD #

WITH unit_grp_map AS (
    -- 按单元和计算编码聚合GRP_CD与对应金额
    SELECT 
        UnitNumber,
        CALC_CD,
        LISTAGG(GRP_CD || '|' || GRP_AMT, ',') WITHIN GROUP (ORDER BY GRP_CD) AS grp_kv
    FROM UnitData
    GROUP BY UnitNumber, CALC_CD
),
formula_replace AS (
    -- 拆分GRP_CD键值对并关联基础公式
    SELECT 
        u.UnitNumber,
        u.CALC_CD,
        c.FORMULA1,
        c.FORMULA2,
        c.FORMULA3,
        c.FORMULA4,
        REGEXP_SUBSTR(u.grp_kv, '[^,]+', 1, LEVEL) AS kv_pair
    FROM unit_grp_map u
    JOIN Calc c ON u.CALC_CD = c.CALC_CD
    CONNECT BY LEVEL <= REGEXP_COUNT(u.grp_kv, ',') + 1
        AND PRIOR u.UnitNumber = u.UnitNumber
        AND PRIOR u.CALC_CD = u.CALC_CD
        AND PRIOR SYS_GUID() IS NOT NULL
),
recursive_replace AS (
    -- 递归替换每个公式中的GRP_CD
    SELECT 
        UnitNumber,
        CALC_CD,
        REPLACE(FORMULA1, SUBSTR(kv_pair, 1, INSTR(kv_pair, '|')-1), SUBSTR(kv_pair, INSTR(kv_pair, '|')+1)) AS FORMULA1,
        REPLACE(FORMULA2, SUBSTR(kv_pair, 1, INSTR(kv_pair, '|')-1), SUBSTR(kv_pair, INSTR(kv_pair, '|')+1)) AS FORMULA2,
        FORMULA3,
        FORMULA4,
        LEVEL AS replace_level
    FROM formula_replace
    WHERE LEVEL = 1
    UNION ALL
    SELECT 
        r.UnitNumber,
        r.CALC_CD,
        REPLACE(r.FORMULA1, SUBSTR(f.kv_pair, 1, INSTR(f.kv_pair, '|')-1), SUBSTR(f.kv_pair, INSTR(f.kv_pair, '|')+1)) AS FORMULA1,
        REPLACE(r.FORMULA2, SUBSTR(f.kv_pair, 1, INSTR(f.kv_pair, '|')-1), SUBSTR(f.kv_pair, INSTR(f.kv_pair, '|')+1)) AS FORMULA2,
        r.FORMULA3,
        r.FORMULA4,
        r.replace_level + 1
    FROM recursive_replace r
    JOIN formula_replace f ON r.UnitNumber = f.UnitNumber AND r.CALC_CD = f.CALC_CD
    WHERE r.replace_level + 1 = f.LEVEL
),
final_formulas AS (
    -- 取每个单元替换完成的最终公式
    SELECT 
        UnitNumber,
        CALC_CD,
        FORMULA1,
        FORMULA2,
        FORMULA3,
        FORMULA4
    FROM recursive_replace
    WHERE replace_level = (SELECT MAX(replace_level) FROM recursive_replace rr WHERE rr.UnitNumber = recursive_replace.UnitNumber AND rr.CALC_CD = recursive_replace.CALC_CD)
)

2. 计算公式结果并插入结果表 #

INSERT INTO Calc_Result (UnitNumber, CALC_CD, Formula1, Formula2, Formula3, Formula4, FORMULA_RESULT)
SELECT 
    ff.UnitNumber,
    ff.CALC_CD,
    ff.FORMULA1,
    ff.FORMULA2,
    REPLACE(ff.FORMULA3, 'Part1', TO_CHAR(f1_result)) AS FORMULA3,
    REPLACE(REPLACE(ff.FORMULA4, 'Part2', TO_CHAR(f2_result)), 'Part3', TO_CHAR(f3_result)) AS FORMULA4,
    f4_result AS FORMULA_RESULT
FROM final_formulas ff
-- 计算Formula1结果
CROSS APPLY (SELECT EXECUTE IMMEDIATE 'SELECT ' || ff.FORMULA1 FROM DUAL) f1(f1_result)
-- 计算Formula2结果
CROSS APPLY (SELECT EXECUTE IMMEDIATE 'SELECT ' || ff.FORMULA2 FROM DUAL) f2(f2_result)
-- 计算Formula3结果
CROSS APPLY (SELECT EXECUTE IMMEDIATE 'SELECT ' || REPLACE(ff.FORMULA3, 'Part1', TO_CHAR(f1_result)) FROM DUAL) f3(f3_result)
-- 计算Formula4最终结果
CROSS APPLY (SELECT EXECUTE IMMEDIATE 'SELECT ' || REPLACE(REPLACE(ff.FORMULA4, 'Part2', TO_CHAR(f2_result)), 'Part3', TO_CHAR(f3_result)) FROM DUAL) f4(f4_result);

性能优化补充建议 #

• 启用并行查询：在SQL语句前添加/*+ PARALLEL(8) */（根据服务器CPU核心数调整并行度），利用Oracle并行处理能力加速大表聚合和替换。
• 索引优化：为UnitData表的UnitNumber和CALC_CD字段创建复合索引，提升聚合查询的效率。
• 动态SQL安全校验：若公式内容存在不可控输入，需先校验表达式合法性，避免SQL注入风险。

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