CODEWITHSUNDEEP
oracle-databasetemp-tables
N. Kaufman
N. Kaufman

Reputation: 91

Oracle use temporary table or some other method

In Oracle, Need to re-use results of a query multiple times in a stored proc. Wondering if temp table is the recommended way or something else...

First I create a resultset based on certain input params.

Then do some checks to see if any of the resultset rows appear in Table 1. Simple join and count would suffice. If count > 0 then return. If count == 0 then I insert selected values from resultset (based on some condition) to Table 1.

Should one go with temp table or something else for optimal performance?

Thanks,

Upvotes: 0

Views: 699

Answers (1)

alexgibbs
alexgibbs

Reputation: 2480

In light of the last update, it appears that you'll need to make qualitatively different inserts into multiple tables, and use the result of an expensive query in multiple (different) tests.

It is always best to test and benchmark (comparing the upsides and downsides, along with performance of the temp table vs other approaches). I'll include an alternative style here, that just pulls the results into memory, where they can be queried against as needed (or iterated-over, counted, etc.)

An example is below:

-- Example Data:

CREATE TABLE TABLE_1 (LOREM_IPSUM NUMBER);
INSERT INTO TABLE_1 VALUES (6);
COMMIT;

-- Custom Types to hold the data

CREATE OR REPLACE TYPE EXPENSIVE_QUERY_TYPE IS OBJECT(LOREM_IPSUM NUMBER);
/
CREATE OR REPLACE TYPE EXPENSIVE_QUERY_RESULT IS TABLE OF EXPENSIVE_QUERY_TYPE;
/

-- Example block:

DECLARE
    V_EXPENSIVE_RESULT EXPENSIVE_QUERY_RESULT := EXPENSIVE_QUERY_RESULT();
    V_TABLE_1_TEST INTEGER;
BEGIN
    SELECT EXPENSIVE_QUERY_TYPE(LEVEL)
    BULK COLLECT INTO V_EXPENSIVE_RESULT
    FROM DUAL
    CONNECT BY LEVEL <= 5;

    SELECT COUNT(*) INTO V_TABLE_1_TEST
        FROM TABLE_1
            WHERE EXISTS(SELECT 1 FROM TABLE(V_EXPENSIVE_RESULT) WHERE LOREM_IPSUM = TABLE_1.LOREM_IPSUM);

    IF V_TABLE_1_TEST > 0
        THEN
        RETURN;
        ELSE
        INSERT INTO TABLE_1
            SELECT LOREM_IPSUM FROM TABLE(V_EXPENSIVE_RESULT)
            WHERE MOD(LOREM_IPSUM,2) = 0;
    END IF;

END;
/

When TABLE_1 initially has only a single record of value 6, this inserts 2 and 4 (since there are no shared data).

...PL/SQL procedure successfully completed.
SELECT * FROM TABLE_1;
   LOREM_IPSUM
______________
             2
             4
             6

But if it includes anything in the expensive_query (such as initially including both 3 and 6), nothing is inserted: 

...PL/SQL procedure successfully completed.
SELECT * FROM TABLE_1;
   LOREM_IPSUM
______________
             3
             6

Upvotes: 1

Related Questions