存储过程中复用SELECT语句优化咨询：替代临时表的更优方案？

Great question—let’s tackle this based on your core goals: ditching slow row-by-row loops, running that expensive query only once, and keeping your logic centralized. Let’s break down the best options, including CTEs (WITH clauses), temporary tables, and a high-performance PL/SQL alternative.

Option 1: PL/SQL Bulk Operations (Best for Most Cases) #

If you’re working in Oracle (since your original code uses dual), using bulk collections is the most efficient way to replace row-by-row loops. This approach runs your expensive query once, stores results in an in-memory collection, then uses batch operations to handle inserts/updates—no disk I/O needed for temporary storage.

Here’s how it works:

DECLARE
    -- Define a collection type to hold your query results
    TYPE some_value_tab IS TABLE OF NUMBER;
    v_some_values some_value_tab;
BEGIN
    -- Run the expensive query ONCE, bulk-collect results into memory
    SELECT some_value
    BULK COLLECT INTO v_some_values
    FROM (
        SELECT 1 some_value, 2 some_other_value FROM dual
        UNION
        SELECT 3 some_value, 4 some_other_value FROM dual
    );

    -- Batch insert into table1 (way faster than row-by-row)
    FORALL i IN 1..v_some_values.COUNT
        INSERT INTO table1 (field1) VALUES (v_some_values(i));

    -- Batch update table2 using the collection
    UPDATE table2
    SET field2 = 5
    WHERE field1 IN (SELECT column_value FROM TABLE(v_some_values));

    COMMIT; -- Optional, depending on your transaction needs
END;
/

• Pros: Runs your query exactly once, minimal overhead, way faster than loops, logic stays in one place.
• Cons: Not ideal for extremely large result sets (if the collection exceeds memory limits).

Option 2: Temporary Tables (For Large Datasets) #

If your query returns too much data to fit in memory, a temporary table is a solid choice. It stores the query result on disk, lets you reuse it across multiple DML operations, and even supports indexes to speed up subsequent updates/filters.

For Oracle, use a session-level global temporary table (only needs to be created once, data persists for your session):

-- Create the temp table (run once, not every time)
CREATE GLOBAL TEMPORARY TABLE tmp_table (
    some_value NUMBER,
    some_other_value NUMBER
) ON COMMIT PRESERVE ROWS;

-- Run your expensive query ONCE and populate the temp table
INSERT INTO tmp_table (some_value, some_other_value)
SELECT 1 some_value, 2 some_other_value FROM dual
UNION
SELECT 3 some_value, 4 some_other_value FROM dual;

-- Reuse the temp table for insert
INSERT INTO table1 (field1)
SELECT some_value FROM tmp_table;

-- Reuse for update
UPDATE table2
SET field2 = 5
WHERE field1 IN (SELECT some_value FROM tmp_table);

-- No need to drop: temp table data is cleared automatically when your session ends

• Pros: Handles large datasets, supports indexes for faster filtering, easy to debug (you can query the temp table mid-process).
• Cons: Requires upfront setup of the temp table, adds disk I/O overhead.

Option 3: CTE (WITH Clause) – Syntax-Focused Choice #

You mentioned wanting to use a WITH clause, which works well for simpler workflows—but you need to ensure the CTE isn’t re-executed for each DML operation. Most databases (including Oracle) can optimize this, but you can force materialization to guarantee the query runs once.

Here’s an Oracle example with a materialized CTE:

WITH tmp_statement AS (
    SELECT /*+ MATERIALIZE */ some_value, some_other_value
    FROM (
        SELECT 1 some_value, 2 some_other_value FROM dual
        UNION
        SELECT 3 some_value, 4 some_other_value FROM dual
    )
)
INSERT INTO table1 (field1)
SELECT some_value FROM tmp_statement;

-- Then reuse the CTE for update (note: in Oracle, wrap in a PL/SQL block to run multiple DMLs with the same CTE)
DECLARE
BEGIN
    WITH tmp_statement AS (
        SELECT /*+ MATERIALIZE */ some_value FROM (
            SELECT 1 some_value FROM dual UNION SELECT 3 some_value FROM dual
        )
    )
    UPDATE table2 SET field2 = 5 WHERE field1 IN (SELECT some_value FROM tmp_statement);
END;
/

• Pros: Clean, concise syntax, no object creation needed.
• Cons: Not all databases support reusing the same CTE across multiple DMLs without re-running the query; materialization hints are database-specific.

Which Should You Choose? #

1. Go with PL/SQL bulk operations if your result set fits in memory—it’s the fastest, most efficient option that meets your "single query" requirement.
2. Use a temporary table if your dataset is too large for memory, or if you need to add indexes to optimize subsequent operations.
3. Stick with CTEs only if syntax simplicity is your top priority, and verify your database’s optimizer will materialize the CTE to avoid re-running the expensive query.

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