航空领域ALL_TICKETS表ALLIANCE_FLG批量更新优化及报错排查

需求对应更新规则 #

只要用户满足以下任一条件，就将其所有ALL_TICKETS记录的ALLIANCE_FLG字段更新为'Y'：

• 该用户已有任意一张机票的ALLIANCE_FLG为'Y'
• 该用户在ALL_USERS表中的ONEWRLD字段为'Y'
• 该用户存在既往机票的ALLIANCE_FLG字段为空

核心批量更新脚本 #

针对百万级数据量，用BULK COLLECT+FORALL减少PL/SQL与SQL引擎的上下文切换，同时分批次处理避免内存溢出：

DECLARE
    -- 筛选符合条件的用户ID游标
    CURSOR c_eligible_users IS
        SELECT DISTINCT t.user_id
        FROM ALL_TICKETS t
        LEFT JOIN ALL_USERS u ON t.user_id = u.user_id
        WHERE 
            -- 条件1：用户已有机票标记为Y
            EXISTS (SELECT 1 FROM ALL_TICKETS t2 WHERE t2.user_id = t.user_id AND t2.ALLIANCE_FLG = 'Y')
            -- 条件2：用户属于寰宇一家会员
            OR u.ONEWRLD = 'Y'
            -- 条件3：用户有空标记的既往机票
            OR EXISTS (SELECT 1 FROM ALL_TICKETS t3 WHERE t3.user_id = t.user_id AND t3.ALLIANCE_FLG IS NULL);
    
    TYPE user_id_tab IS TABLE OF ALL_TICKETS.user_id%TYPE;
    v_user_ids user_id_tab;
    v_batch_size CONSTANT PLS_INTEGER := 10000; -- 可根据服务器内存调整，建议5000-20000
BEGIN
    OPEN c_eligible_users;
    LOOP
        -- 批量获取一批用户ID
        FETCH c_eligible_users BULK COLLECT INTO v_user_ids LIMIT v_batch_size;
        EXIT WHEN v_user_ids.COUNT = 0;
        
        -- 批量更新这批用户的所有机票记录
        FORALL i IN 1..v_user_ids.COUNT
            UPDATE ALL_TICKETS
            SET ALLIANCE_FLG = 'Y'
            WHERE user_id = v_user_ids(i);
        
        COMMIT; -- 每批次提交，避免大事务占用过多资源
    END LOOP;
    CLOSE c_eligible_users;
    
    DBMS_OUTPUT.PUT_LINE('更新完成，累计处理 ' || c_eligible_users%ROWCOUNT || ' 个用户的机票记录');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('更新出错：' || SQLERRM);
        ROLLBACK;
        RAISE;
END;
/

性能优化补充说明 #

1. 批次大小调整：v_batch_size可根据数据库服务器内存灵活调整，平衡内存占用和处理效率
2. 去重用户ID：用DISTINCT先筛选出需要更新的用户列表，避免重复处理同一用户的多条机票记录
3. 分批提交：每批次提交事务，减少undo空间占用，降低锁冲突风险

超大数据量可选方案：临时表中转 #

如果用户量极大，可先把符合条件的用户ID存入临时表，再基于临时表批量更新，进一步提升效率：

-- 创建会话级临时表（仅当前会话可见，会话结束自动清除）
CREATE GLOBAL TEMPORARY TABLE TMP_ELIGIBLE_USERS (
    user_id ALL_TICKETS.user_id%TYPE PRIMARY KEY
) ON COMMIT PRESERVE ROWS;

-- 插入符合条件的用户ID
INSERT INTO TMP_ELIGIBLE_USERS (user_id)
SELECT DISTINCT t.user_id
FROM ALL_TICKETS t
LEFT JOIN ALL_USERS u ON t.user_id = u.user_id
WHERE 
    EXISTS (SELECT 1 FROM ALL_TICKETS t2 WHERE t2.user_id = t.user_id AND t2.ALLIANCE_FLG = 'Y')
    OR u.ONEWRLD = 'Y'
    OR EXISTS (SELECT 1 FROM ALL_TICKETS t3 WHERE t3.user_id = t.user_id AND t3.ALLIANCE_FLG IS NULL);

-- 基于临时表批量更新
DECLARE
    CURSOR c_temp_users IS
        SELECT user_id FROM TMP_ELIGIBLE_USERS;
    
    TYPE user_id_tab IS TABLE OF TMP_ELIGIBLE_USERS.user_id%TYPE;
    v_user_ids user_id_tab;
    v_batch_size CONSTANT PLS_INTEGER := 10000;
BEGIN
    OPEN c_temp_users;
    LOOP
        FETCH c_temp_users BULK COLLECT INTO v_user_ids LIMIT v_batch_size;
        EXIT WHEN v_user_ids.COUNT = 0;
        
        FORALL i IN 1..v_user_ids.COUNT
            UPDATE ALL_TICKETS
            SET ALLIANCE_FLG = 'Y'
            WHERE user_id = v_user_ids(i);
        
        COMMIT;
    END LOOP;
    CLOSE c_temp_users;
    
    DBMS_OUTPUT.PUT_LINE('更新完成，累计处理 ' || c_temp_users%ROWCOUNT || ' 个用户的机票记录');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('更新出错：' || SQLERRM);
        ROLLBACK;
        RAISE;
END;
/

-- 手动清理临时表（可选）
TRUNCATE TABLE TMP_ELIGIBLE_USERS;

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