数仓工具-基于临时表 + 表交换（SWAP）的增量刷新机制，目的是提升查询性能、支持增量更新、避免每次查询都执行昂贵的递归操作。

✅ 目标：实现“增量刷新”的临时表方案

使用一张物化表（T_BOM_MAPPING）存储结果。
使用一张临时工作表（T_BOM_MAPPING_TMP）用于构建新数据。
利用 ALTER TABLE ... EXCHANGE PARTITION 或 RENAME 交换表实现原子性刷新。
支持增量更新检测（通过时间戳或版本字段判断变更）。
定期通过存储过程或调度任务刷新。

✅ 一、前提假设

表 bmb_file 和 bma_file 有更新时间字段（如 last_update_time），用于判断是否需要刷新。
- 若无，建议添加，或使用数据库的 CDC/触发器机制。
使用 Oracle 数据库（因使用 CONNECT BY 语法）。
允许每日/定时刷新（非实时）。

✅ 二、完整实现步骤

第1步：创建物化表（主表）

CREATE TABLE T_BOM_MAPPING (
    WULIAO_MAIN     VARCHAR2(30) NOT NULL,
    WULIAO_SINGLE   VARCHAR2(30) NOT NULL,
    CREATE_TIME     DATE DEFAULT SYSDATE
);
-- 可选：创建索引
CREATE INDEX IDX_BOM_MAP_MAIN ON T_BOM_MAPPING(WULIAO_MAIN);
CREATE INDEX IDX_BOM_MAP_SINGLE ON T_BOM_MAPPING(WULIAO_SINGLE);

第2步：创建临时工作表（结构与主表一致）

CREATE TABLE T_BOM_MAPPING_TMP (
    WULIAO_MAIN     VARCHAR2(30) NOT NULL,
    WULIAO_SINGLE   VARCHAR2(30) NOT NULL,
    CREATE_TIME     DATE DEFAULT SYSDATE
);
-- 索引可选，也可在交换后重建

⚠️ 注意：临时表必须与主表结构完全一致（包括约束、索引等），否则 EXCHANGE 会失败。

第3步：创建刷新存储过程（核心逻辑）

CREATE OR REPLACE PROCEDURE P_REFRESH_BOM_MAPPING AS
BEGIN
    -- Step 1: 清空临时表
    EXECUTE IMMEDIATE 'TRUNCATE TABLE T_BOM_MAPPING_TMP';

    -- Step 2: 插入新计算结果到临时表
    INSERT INTO T_BOM_MAPPING_TMP (WULIAO_MAIN, WULIAO_SINGLE)
    WITH bom_recursive AS (
        SELECT
            CONNECT_BY_ROOT bmb01 AS wuliao_main,
            bmb03 AS wuliao_single,
            LEVEL AS bmb_level
        FROM bmb_file
        WHERE (bmb05 IS NULL OR bmb04 > bmb05)
          AND bmb01 IS NOT NULL
        START WITH bmb01 IN (
            SELECT bma01
            FROM bma_file
            WHERE bma01 IS NOT NULL
        )
        CONNECT BY PRIOR bmb03 = bmb01
    ),
    bom_with_root AS (
        SELECT wuliao_main, wuliao_single FROM bom_recursive
        UNION ALL
        SELECT bma01 AS wuliao_main, bma01 AS wuliao_single
        FROM bma_file
        WHERE bma01 IN (SELECT DISTINCT wuliao_main FROM bom_recursive)
    )
    SELECT DISTINCT
        wuliao_main,
        wuliao_single
    FROM bom_with_root
    WHERE wuliao_main IS NOT NULL
      AND wuliao_single IS NOT NULL;

    -- Step 3: 提交事务
    COMMIT;

    -- Step 4: 原子性交换表（关键步骤）
    -- 使用 RENAME 实现原子交换（推荐，简单可靠）
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE T_BOM_MAPPING_OLD PURGE';
    EXCEPTION
        WHEN OTHERS THEN NULL; -- 忽略表不存在错误
    END;

    -- 重命名现有主表为旧表，临时表为新主表
    EXECUTE IMMEDIATE 'RENAME T_BOM_MAPPING TO T_BOM_MAPPING_OLD';
    EXECUTE IMMEDIATE 'RENAME T_BOM_MAPPING_TMP TO T_BOM_MAPPING';

    -- Step 5: 可选：删除旧表
    EXECUTE IMMEDIATE 'DROP TABLE T_BOM_MAPPING_OLD PURGE';

    -- 或者保留旧表用于回滚
    -- EXECUTE IMMEDIATE 'RENAME T_BOM_MAPPING_OLD TO T_BOM_MAPPING_TMP'; -- 回滚用

    DBMS_OUTPUT.PUT_LINE('BOM Mapping 刷新完成：' || TO_CHAR(SYSDATE, 'YYYY-MM-DD HH24:MI:SS'));
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('刷新失败：' || SQLERRM);
        RAISE;
END P_REFRESH_BOM_MAPPING;
/

第4步：创建视图（指向物化表）

CREATE OR REPLACE VIEW V_BOM_MAPPING AS
SELECT WULIAO_MAIN, WULIAO_SINGLE
FROM T_BOM_MAPPING;

此视图不再包含复杂逻辑，仅查询物化表，性能极高。

第5步：调度定时刷新任务（例如每天凌晨）

使用 Oracle Job Scheduler：

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'JOB_REFRESH_BOM_MAPPING',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'BEGIN P_REFRESH_BOM_MAPPING; END;',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=2; BYMINUTE=0; BYSECOND=0', -- 每天凌晨2点
        enabled         => TRUE,
        comments        => '每日刷新BOM物料映射表'
    );
END;
/

第6步（可选）：增量刷新优化（仅当数据量极大时）

若 bmb_file 或 bma_file 更新频率低，可加入变更检测逻辑，避免无意义刷新：

-- 示例：判断是否有最近24小时变更
SELECT COUNT(*) INTO v_cnt
FROM (
    SELECT last_update_time FROM bmb_file WHERE last_update_time > SYSDATE - 1
    UNION ALL
    SELECT last_update_time FROM bma_file WHERE last_update_time > SYSDATE - 1
);
IF v_cnt = 0 THEN
    DBMS_OUTPUT.PUT_LINE('无变更，跳过刷新');
    RETURN;
END IF;

✅ 三、优点总结

优势	说明
🔹 高性能查询	视图直接查物化表，无递归开销
🔹 原子性刷新	`RENAME` 是元数据操作，瞬间完成
🔹 可维护性高	易于调试、索引优化、监控
🔹 支持增量逻辑	可加入变更检测避免无效刷新
🔹 回滚安全	保留旧表可快速回滚

✅ 四、注意事项

主键去重：DISTINCT 已处理，但可考虑加主键约束：

ALTER TABLE T_BOM_MAPPING ADD CONSTRAINT PK_BOM_MAP PRIMARY KEY (WULIAO_MAIN, WULIAO_SINGLE);

　　　　临时表命名规范：建议使用 TMP_ 或 _STG 后缀。
　　　　权限管理：确保执行存储过程的用户有建表、重命名权限。
　　　　归档与监控：记录每次刷新日志（如插入日志表）。

✅ 五、回滚方案（出错时）

-- 如果刷新出错，可手动恢复
RENAME T_BOM_MAPPING TO T_BOM_MAPPING_BAD;
RENAME T_BOM_MAPPING_OLD TO T_BOM_MAPPING;

✅ 最终优化版存储过程（含：增量检测 + 安全回滚机制）

CREATE OR REPLACE PROCEDURE P_REFRESH_BOM_MAPPING AS
    v_cnt        NUMBER;
    v_start_time DATE := SYSDATE;
BEGIN
    -- ========================
    -- ✅ 1. 【增量检测】判断是否有数据变更，决定是否跳过刷新
    -- ========================
    SELECT COUNT(*) INTO v_cnt
    FROM (
        SELECT 1 FROM bmb_file WHERE last_update_time > SYSDATE - 1 AND ROWNUM = 1
        UNION ALL
        SELECT 1 FROM bma_file WHERE last_update_time > SYSDATE - 1 AND ROWNUM = 1
    );

    IF v_cnt = 0 THEN
        DBMS_OUTPUT.PUT_LINE('【增量检测】无最近24小时变更，跳过刷新。');
        RETURN; -- 无变更，提前退出
    END IF;

    DBMS_OUTPUT.PUT_LINE('【开始刷新】检测到BOM数据变更，启动刷新流程...');

    -- ========================
    -- ✅ 2. 清空临时表
    -- ========================
    EXECUTE IMMEDIATE 'TRUNCATE TABLE T_BOM_MAPPING_TMP';

    -- ========================
    -- ✅ 3. 插入新计算结果到临时表
    -- ========================
    INSERT INTO T_BOM_MAPPING_TMP (WULIAO_MAIN, WULIAO_SINGLE)
    WITH bom_recursive AS (
        SELECT
            CONNECT_BY_ROOT bmb01 AS wuliao_main,
            bmb03 AS wuliao_single,
            LEVEL AS bmb_level
        FROM bmb_file
        WHERE (bmb05 IS NULL OR bmb04 > bmb05)
          AND bmb01 IS NOT NULL
        START WITH bmb01 IN (
            SELECT bma01
            FROM bma_file
            WHERE bma01 IS NOT NULL
        )
        CONNECT BY PRIOR bmb03 = bmb01
    ),
    bom_with_root AS (
        SELECT wuliao_main, wuliao_single FROM bom_recursive
        UNION ALL
        SELECT bma01 AS wuliao_main, bma01 AS wuliao_single
        FROM bma_file
        WHERE bma01 IN (SELECT DISTINCT wuliao_main FROM bom_recursive)
    )
    SELECT DISTINCT
        wuliao_main,
        wuliao_single
    FROM bom_with_root
    WHERE wuliao_main IS NOT NULL
      AND wuliao_single IS NOT NULL;

    COMMIT;

    -- ========================
    -- ✅ 4. 原子性交换表（RENAME 实现）
    -- ========================
    -- 先尝试删除上一次的旧表（避免冲突）
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE T_BOM_MAPPING_OLD PURGE';
    EXCEPTION
        WHEN OTHERS THEN NULL;
    END;

    -- 执行交换：主表 → 旧表，临时表 → 主表
    EXECUTE IMMEDIATE 'RENAME T_BOM_MAPPING TO T_BOM_MAPPING_OLD';
    EXECUTE IMMEDIATE 'RENAME T_BOM_MAPPING_TMP TO T_BOM_MAPPING';

    DBMS_OUTPUT.PUT_LINE('【刷新完成】BOM映射表已成功更新。');

    -- 可选：记录日志或发送通知
    -- INSERT INTO refresh_log VALUES ('BOM_MAPPING', v_start_time, SYSDATE, 'SUCCESS');

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;

        -- ========================
        -- ❌ 5. 【异常处理 & 回滚机制】
        -- ========================
        DECLARE
            e_table_exists EXCEPTION;
            PRAGMA EXCEPTION_INIT(e_table_exists, -955); -- ORA-00955: name is already used by an existing object
        BEGIN
            -- 尝试恢复：如果当前主表不存在或损坏，尝试用旧表回滚
            BEGIN
                -- 检查当前主表是否存在
                EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM DUAL WHERE EXISTS (SELECT 1 FROM USER_TABLES WHERE TABLE_NAME = ''T_BOM_MAPPING'')';
            EXCEPTION
                WHEN OTHERS THEN
                    -- 当前主表可能不存在，尝试恢复
                    BEGIN
                        EXECUTE IMMEDIATE 'RENAME T_BOM_MAPPING_OLD TO T_BOM_MAPPING';
                        DBMS_OUTPUT.PUT_LINE('【回滚成功】已恢复至旧版本表。');
                        RAISE_APPLICATION_ERROR(-20001, '刷新失败且主表丢失，已自动回滚：' || SQLERRM);
                    EXCEPTION
                        WHEN e_table_exists THEN
                            DBMS_OUTPUT.PUT_LINE('【回滚失败】T_BOM_MAPPING 已存在，无法恢复旧表。');
                        WHEN OTHERS THEN
                            DBMS_OUTPUT.PUT_LINE('【回滚失败】无法恢复 T_BOM_MAPPING_OLD：' || SQLERRM);
                    END;
            END;

            -- 记录错误日志
            DBMS_OUTPUT.PUT_LINE('【刷新失败】错误信息：' || SQLERRM);
            RAISE_APPLICATION_ERROR(-20000, 'BOM映射刷新失败：' || SQLERRM);

        END;
END P_REFRESH_BOM_MAPPING;
/

第一次执行存储过程：

- Step 1: 创建表（如果还没建）
-- （略，参考之前语句）

-- Step 2: 临时修改存储过程：注释增量检测（仅首次）
-- （修改后编译存储过程）

-- Step 3: 执行存储过程  第一次执行存储过程
BEGIN
    P_REFRESH_BOM_MAPPING;
END;
/

-- Step 4: 验证数据
SELECT COUNT(*) FROM T_BOM_MAPPING;
SELECT * FROM V_BOM_MAPPING WHERE ROWNUM <= 10;

-- Step 5: 恢复存储过程：重新启用增量检测（添加字段后）
-- 然后重新编译存储过程

✅ 总结：你应该这样理解流程

深色版本

开始
│
├─▶ 增量检测 → 无变更？→ 结束
│
├─▶ 有变更 → 清空临时表 → 计算新数据 → 插入临时表
│
├─▶ 交换表（主 ←→ 临时）
│
├─▶ 成功：完成
│
└─▶ 失败：尝试用旧表恢复主表 → 抛出错误

✅ 总结

你已成功将一个复杂的递归视图改造为：

“物化表 + 临时表 + 交换刷新” 的高性能、可维护架构。

📌 最终访问方式不变：应用仍查询 V_BOM_MAPPING，但底层已是优化结构。

posted @ 2025-08-19 13:15 三生有幸格格阅读(16) 评论(0) 收藏举报

刷新页面返回顶部