Oracle 12c存储过程中的辅助表是否应改用全局临时表

• 现有Oracle 12c存储过程的常规结构：

PROCEDURE xxx AS 
   -- 变量声明
BEGIN
   -- 记录执行日志
   -- 创建1号中间计算表
   -- 记录执行日志
   -- 创建2号中间计算表
   -- 记录执行日志
   -- ...
   -- 创建最终结果表
   -- 记录执行日志
END xxx

• 当前实现方式：1号到n-1号表为普通持久表，用于拆分复杂计算逻辑、优化执行性能，最终产出第n张结果表
• 核心疑问：用普通表做中间存储是否合理？是否需要改用全局临时表？
• 附实际业务代码：

create or replace PACKAGE BODY balance_mismatch_dca AS
    idproceduregeneral VARCHAR2(50);

    PROCEDURE aux_tables AS
        balance_zufi_dca_exists     INTEGER;
        balance_dfkkop_dca_exists   INTEGER;
    BEGIN
        idproceduregeneral := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1));
        uxxxxxx_logs('Start Aux Tables', idproceduregeneral);
        -- 检查第一辅助表是否存在，存在则删除重建
        SELECT COUNT(*)
        INTO balance_zufi_dca_exists
        FROM sys.all_tables
        WHERE table_name = upper('balance_zufi_dca');

        IF balance_zufi_dca_exists = 1 THEN
            EXECUTE IMMEDIATE 'Drop Table balance_zufi_dca';
        END IF;
        EXECUTE IMMEDIATE q'[
            CREATE TABLE balance_zufi_dca AS
            WITH aux AS (
                SELECT vkont, vtref, MAX(zzmfvdat) AS lat
                FROM cdc.uap_zufi_t_dca_place@rbip
                WHERE zzextamount != 0
                GROUP BY vkont, vtref
            ), aux2 AS (
                SELECT a.vkont, a.vtref, a.lat,
                       MAX(b.zzextamount) AS amount_zufi,
                       MAX(b.zzplacref) AS dca,
                       MAX(zzplace_typ) AS placement
                FROM aux a
                JOIN cdc.uap_zufi_t_dca_place@rbip b 
                  ON b.vkont = a.vkont
                 AND b.vtref = a.vtref
                 AND b.zzmfvdat = a.lat
                GROUP BY a.vkont, a.vtref, a.lat
            )
            SELECT vkont,
                   SUM(amount_zufi) AS amount_zufi,
                   MAX(dca) AS dca,
                   MAX(placement) AS placement
            FROM aux2
            GROUP BY vkont
        ]';
        uxxxxxx_logs('End Balance Zufi DCA', idproceduregeneral);
        -- 检查第二辅助表是否存在，存在则删除重建
        SELECT COUNT(*)
        INTO balance_dfkkop_dca_exists
        FROM sys.all_tables
        WHERE table_name = upper('balance_dfkkop_dca');

        IF balance_dfkkop_dca_exists = 1 THEN
            EXECUTE IMMEDIATE 'Drop Table balance_dfkkop_dca';
        END IF;
        EXECUTE IMMEDIATE q'[
            Create Table balance_dfkkop_dca AS
            WITH aux AS (
                SELECT /*+ FULL(a) */
                       vkont,
                       betrw as amount_dfkkop
                FROM cdc.uap_dfkkop@rbip a
                WHERE augrs IS NULL
                  AND augst IS NULL
                  AND stakz IS NULL
                  AND abwbl IS NULL
                  AND (studt <= to_char(sysdate, 'YYYYMMDD') or studt is null)
                  AND faedn <= to_char(sysdate, 'YYYYMMDD')
            )
            SELECT * FROM aux
        ]';
        uxxxxxx_logs('End Balance DFKKOP DCA', idproceduregeneral);
    END aux_tables;

    PROCEDURE gen_view AS
    BEGIN
        idproceduregeneral := utl_call_stack.concatenate_subprogram(utl_call_stack.subprogram(1));
        -- 基于辅助表创建统计类永久视图
        EXECUTE IMMEDIATE q'[
            CREATE OR REPLACE VIEW vw_bm_dca_volumes AS
            WITH aux AS (
                SELECT a.vkont, a.amount_zufi,
                       SUM(nvl(b.amount_dfkkop, 0)) AS amount_dfkkop
                FROM balance_zufi_dca a
                LEFT JOIN balance_dfkkop_dca b ON b.vkont = a.vkont
                GROUP BY a.vkont, a.amount_zufi
            )
            SELECT CASE
                       WHEN amount_zufi = amount_dfkkop THEN 'Chased for the Correct Balance'
                       WHEN amount_zufi > amount_dfkkop THEN 'Chased for Higher Balance'
                       WHEN amount_zufi < amount_dfkkop THEN 'Chased for Lower Balance'
                       ELSE NULL
                   END AS status,
                   COUNT(1) AS ca_count,
                   round(RATIO_TO_REPORT(COUNT(1)) OVER() * 100, 2) perc
            FROM aux
            GROUP BY CASE
                WHEN amount_zufi = amount_dfkkop THEN 'Chased for the Correct Balance'
                WHEN amount_zufi > amount_dfkkop THEN 'Chased for Higher Balance'
                WHEN amount_zufi < amount_dfkkop THEN 'Chased for Lower Balance'
                ELSE NULL
            END
            ORDER BY COUNT(1) DESC
        ]';
        -- 基于辅助表创建明细类永久视图
        EXECUTE IMMEDIATE q'[
            CREATE OR REPLACE VIEW vw_bm_dca_data AS
            WITH aux AS (
                SELECT a.vkont, a.dca, a.placement, a.amount_zufi,
                       SUM(nvl(b.amount_dfkkop, 0)) AS amount_dfkkop
                FROM balance_zufi_dca a
                LEFT JOIN balance_dfkkop_dca b ON b.vkont = a.vkont
                GROUP BY a.vkont, a.dca, a.placement, a.amount_zufi
            )
            SELECT * FROM aux
            WHERE amount_zufi != amount_dfkkop
        ]';
        uxxxxxx_logs('End Views', idproceduregeneral);
    END gen_view;
END balance_mismatch_dca;

当前普通表实现的固有问题 #

• 固定DDL开销高：每次执行都要走完「检查表存在性 -> 删除旧表 -> CTAS建新表」的全流程，每次建表都会触发磁盘空间分配、高水位初始化、统计信息刷新的固定开销，执行效率低
• 并发冲突严重：Drop/Create普通表的DDL操作会获取表排他锁，多会话并发触发存储过程时，会直接出现锁等待、执行失败的问题
• 残留风险高：如果存储过程执行中途报错中断，之前创建的普通表不会自动清理，既占存储空间，也可能被其他会话误访问
• 元数据冗余：普通表定义会被数据字典永久记录，长期运行会积累大量废弃表定义，提升数据库元数据维护成本

全局临时表的适配性说明 #

全局临时表的固有特性完全适配纯计算场景的中间存储需求：

• 无重复DDL开销：全局临时表仅需创建一次，表结构永久存在，每次执行仅需写入数据，不需要反复删除、重建表结构
• 天然支持并发：不同会话访问同一张全局临时表时，只能看到各自写入的独立数据集，完全不会互相阻塞
• 自动清理无残留：根据定义的「提交时清空」「会话断开时清空」规则，数据会在对应节点自动释放，不会留下垃圾数据，也不会出现高水位反复增长的问题
• 性能无损耗：全局临时表的访问性能与普通堆表基本一致，完全可以满足拆分复杂计算、优化执行计划的需求

关键适配误区：现有代码会基于辅助表创建永久视图。如果直接把辅助表替换为全局临时表，会出现逻辑错误：全局临时表的数据仅对写入数据的当前会话可见，当其他会话后续访问永久视图时，会因为读不到任何数据返回空结果，完全丧失查询能力。

针对当前代码的优化方向 #

• 如果业务要求永久视图可以被所有会话访问、查询：不需要替换为全局临时表，直接把所有中间计算逻辑合并到视图的CTE定义中，去掉中间表存储环节即可。Oracle优化器会自动对多层嵌套CTE做执行路径优化，多数场景下性能不会弱于拆成中间表的实现。
• 如果存储过程仅在单会话内完成计算、结果直接返回调用方，不需要后续其他会话访问中间结果：可以把两个辅助表改建为提交时自动清空的全局临时表，提前一次性定义好表结构，替换原有Drop/Create逻辑，改用直接INSERT的方式灌入数据。
• 如果单步计算的CTE执行计划偏移、单步执行耗时过长：才需要用中间存储拆分计算逻辑，此时选择全局临时表的综合成本远低于反复Drop/Create普通表。

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