Oracle Union All查询疑问：满足条件时是否扫描后续大表？

首先直接回答你的核心疑问：默认情况下，Oracle在执行UNION ALL时会先扫描所有参与的表，将结果全部拼接后再进行筛选取前500行。也就是说，哪怕table1已经返回了500条符合条件的数据，Oracle依然会扫描table2和table3，这对于大表table3来说会造成严重的性能浪费，完全不符合你“按需扫描”的需求。

接下来给你几个更优的方案，从纯SQL到PL/SQL，覆盖不同场景：

方案1：纯SQL分层查询（无需PL/SQL） #

通过CTE（公共表表达式）先计算各表符合条件的行数，再按需获取数据，避免不必要的表扫描。这种方案可以直接在SQL客户端执行，不需要编写存储过程：

WITH t1_metrics AS (
    -- 获取table1符合条件的行数和排序后的ID列表
    SELECT COUNT(*) AS row_count,
           CAST(COLLECT(id ORDER BY id DESC) AS SYS.ODCINUMBERLIST) AS sorted_ids
    FROM table1 
    WHERE your_where_condition  -- 替换成你的实际查询条件
),
t2_metrics AS (
    -- 获取table2符合条件的行数和排序后的ID列表
    SELECT COUNT(*) AS row_count,
           CAST(COLLECT(id ORDER BY id DESC) AS SYS.ODCINUMBERLIST) AS sorted_ids
    FROM table2 
    WHERE your_where_condition
)
-- 先取table1的前500条
SELECT * FROM table1 
WHERE id IN (SELECT COLUMN_VALUE FROM TABLE((SELECT sorted_ids FROM t1_metrics)))
UNION ALL
-- 如果table1不够500，再取table2的剩余数量
SELECT * FROM table2 
WHERE id IN (
    SELECT COLUMN_VALUE FROM TABLE((SELECT sorted_ids FROM t2_metrics))
    WHERE ROWNUM <= GREATEST(500 - (SELECT row_count FROM t1_metrics), 0)
)
UNION ALL
-- 如果table1+table2还不够500，再取table3的剩余数量
SELECT * FROM table3 
WHERE your_where_condition
ORDER BY id DESC
FETCH FIRST GREATEST(500 - (SELECT row_count FROM t1_metrics) - (SELECT row_count FROM t2_metrics), 0) ROWS ONLY
-- 最后确保总数量不超过500
FETCH FIRST 500 ROWS ONLY;

优点： #

• 纯SQL实现，无需依赖PL/SQL环境
• 通过CTE缓存各表的统计数据，避免重复查询
• 严格控制只扫描需要的表数据

缺点： #

• 当your_where_condition复杂时，CTE中的计数查询可能会有额外开销（但远小于扫描全表）

方案2：PL/SQL逻辑判断（性能最优） #

如果你的场景对性能要求极高，PL/SQL是更好的选择——它可以通过逻辑判断完全跳过不需要扫描的表，从根源上避免性能浪费：

DECLARE
    v_t1_count NUMBER;
    v_remaining_rows NUMBER;
    v_result SYS_REFCURSOR;
BEGIN
    -- 第一步：统计table1符合条件的行数
    SELECT COUNT(*) INTO v_t1_count 
    FROM table1 
    WHERE your_where_condition;

    IF v_t1_count >= 500 THEN
        -- table1足够500条，直接返回前500
        OPEN v_result FOR
            SELECT * FROM table1 
            WHERE your_where_condition 
            ORDER BY id DESC 
            FETCH FIRST 500 ROWS ONLY;
    ELSE
        v_remaining_rows := 500 - v_t1_count;
        
        -- 第二步：统计table2符合条件的行数
        DECLARE
            v_t2_count NUMBER;
        BEGIN
            SELECT COUNT(*) INTO v_t2_count 
            FROM table2 
            WHERE your_where_condition;

            IF v_t2_count >= v_remaining_rows THEN
                -- table1+table2足够500条，返回两者的合并结果
                OPEN v_result FOR
                    SELECT * FROM table1 WHERE your_where_condition ORDER BY id DESC
                    UNION ALL
                    SELECT * FROM table2 WHERE your_where_condition ORDER BY id DESC FETCH FIRST v_remaining_rows ROWS ONLY
                    ORDER BY id DESC;
            ELSE
                v_remaining_rows := v_remaining_rows - v_t2_count;
                -- 三者都需要查询，返回合并后的前500条
                OPEN v_result FOR
                    SELECT * FROM table1 WHERE your_where_condition ORDER BY id DESC
                    UNION ALL
                    SELECT * FROM table2 WHERE your_where_condition ORDER BY id DESC
                    UNION ALL
                    SELECT * FROM table3 WHERE your_where_condition ORDER BY id DESC FETCH FIRST v_remaining_rows ROWS ONLY
                    ORDER BY id DESC;
            END IF;
        END;
    END IF;

    -- 这里可以根据业务需求处理结果，比如返回给应用程序或打印
    -- 示例：循环输出结果
    -- FOR rec IN v_result LOOP
    --     DBMS_OUTPUT.PUT_LINE('ID: ' || rec.id || ' ... ');
    -- END LOOP;
END;
/

优点： #

• 性能最优：完全跳过不需要扫描的表（比如table1够500时，table2和table3根本不会被访问）
• 逻辑清晰，易于维护和扩展
• 可以灵活处理结果集（比如直接返回游标给应用）

缺点： #

• 需要编写PL/SQL代码，依赖Oracle的PL/SQL环境

方案3：优化提示辅助（不推荐作为核心方案） #

你可能会想到用/*+ FIRST_ROWS(500) */提示让Oracle优先返回前几行，但这个提示只是告诉优化器“尽快返回前N行”，并不能阻止Oracle扫描所有参与UNION ALL的表。它可能会让优化器调整执行计划，但无法从根本上避免不必要的表扫描，所以仅建议作为辅助手段，配合前两种方案使用。

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