PLSQL触发器跨表自动插入数据及性能优化问题咨询

原代码核心问题梳理 #

性能瓶颈根源 #

• 处理BT类型的递归CTE错误使用from original_table获取起始值，会读取原表全量数据，原表数据量越大性能越差，甚至会出现重复生成起始值的bug
• 递归CTE生成连续整数的方式效率极低，且受Oracle默认递归深度限制，区间跨度大时会直接报错
• 多余的distinct关键字会触发额外排序去重操作，无意义消耗性能
• 未做数据类型合法性校验，low_value/high_value转数字失败时会直接触发触发器报错

突变表问题原因 #

行级AFTER INSERT触发器执行时，原表original_table正处于修改未提交状态，Oracle禁止行级触发器读写触发语句操作的表，因此会报突变表错误。 #

方案1：优化行级BEFORE触发器（最快落地，性能提升明显） #

直接修复原有写法的问题，将递归生成序列替换为性能更高的CONNECT BY写法，修复原表查询的bug：

create or replace TRIGGER TRG_NAME
    BEFORE INSERT 
    ON original_table
    FOR EACH ROW
DECLARE
    v_low NUMBER;
    v_high NUMBER;
    v_yr_month NUMBER;
BEGIN
    IF :NEW.opt_value = 'BT' THEN
        -- 转换为数字避免隐式转换
        v_low := TO_NUMBER(:NEW.low_value);
        v_high := TO_NUMBER(:NEW.high_value);
        v_yr_month := TO_NUMBER(TO_CHAR(trunc(sysdate), 'YYYYMM'));
        
        -- 用CONNECT BY生成连续序列，性能远高于递归CTE
        INSERT INTO new_values (id_values, yr_month) 
        SELECT TO_CHAR(v_low + LEVEL - 1), v_yr_month
        FROM dual
        CONNECT BY LEVEL <= v_high - v_low + 1;

    ELSIF :NEW.opt_value = 'EQ' THEN
        v_yr_month := TO_NUMBER(TO_CHAR(add_months(trunc(sysdate), -1), 'YYYYMM'));
        INSERT INTO new_values (id_values, yr_month) 
        VALUES (:NEW.high_value, v_yr_month);    
    END IF;
EXCEPTION
    WHEN VALUE_ERROR THEN
        -- 可根据业务需求调整异常处理逻辑，比如记录错误日志
        RAISE_APPLICATION_ERROR(-20001, 'low_value/high_value不是合法数字');
    WHEN OTHERS THEN
        RAISE;
END;
/

方案2：改用AFTER INSERT触发（彻底规避突变表，支持批量插入） #

使用Oracle 11g及以上版本支持的复合触发器，通过临时集合缓存新增数据，在语句级触发阶段批量写入new_values，完全不会触发突变表问题，批量插入场景下性能更好：

create or replace TRIGGER TRG_NAME
FOR INSERT ON original_table
COMPOUND TRIGGER
    -- 定义临时存储结构
    TYPE t_insert_row IS RECORD(
        opt_value CHAR(2),
        low_value VARCHAR2(24),
        high_value VARCHAR2(24)
    );
    TYPE t_insert_list IS TABLE OF t_insert_row INDEX BY PLS_INTEGER;
    g_insert_list t_insert_list;

    -- 行级AFTER触发：缓存新增数据
    AFTER EACH ROW IS
    BEGIN
        g_insert_list(g_insert_list.COUNT + 1).opt_value := :NEW.opt_value;
        g_insert_list(g_insert_list.COUNT).low_value := :NEW.low_value;
        g_insert_list(g_insert_list.COUNT).high_value := :NEW.high_value;
    END AFTER EACH ROW;

    -- 语句级AFTER触发：批量处理插入new_values
    AFTER STATEMENT IS
        v_low NUMBER;
        v_high NUMBER;
        v_yr_month NUMBER;
    BEGIN
        FOR i IN 1 .. g_insert_list.COUNT LOOP
            IF g_insert_list(i).opt_value = 'BT' THEN
                v_low := TO_NUMBER(g_insert_list(i).low_value);
                v_high := TO_NUMBER(g_insert_list(i).high_value);
                v_yr_month := TO_NUMBER(TO_CHAR(trunc(sysdate), 'YYYYMM'));
                
                INSERT INTO new_values (id_values, yr_month) 
                SELECT TO_CHAR(v_low + LEVEL - 1), v_yr_month
                FROM dual
                CONNECT BY LEVEL <= v_high - v_low + 1;
            ELSIF g_insert_list(i).opt_value = 'EQ' THEN
                v_yr_month := TO_NUMBER(TO_CHAR(add_months(trunc(sysdate), -1), 'YYYYMM'));
                INSERT INTO new_values (id_values, yr_month) 
                VALUES (g_insert_list(i).high_value, v_yr_month);  
            END IF;
        END LOOP;
    END AFTER STATEMENT;
END TRG_NAME;
/

额外优化建议 #

• 如果BT类型的区间跨度经常超过1万，建议提前维护一张存储连续整数的辅助表，直接用辅助表关联过滤生成序列，性能比CONNECT BY更高
• 建议增加low_value <= high_value的校验，避免生成无效序列
• 可根据业务场景调整异常处理逻辑，比如错误数据不抛出异常而是记录到日志表，保证插入流程不中断

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