SQL Oracle statement optimized with temporary table

Question

Hello there,

I have optimized a complex Oracle statement using temporary table like this :

original :

SELECT data FROM table WHERE ..complex statement..;

optimized (I can't use WITH keyword, because I deal with < Oracle9i) :

CREATE GLOBAL TEMPORARY TABLE temptab (x NUMBER, y DATE) ON COMMIT DELETE ROWS;
INSERT INTO temptab SELECT * FROM temp;
SELECT data FROM temptab WHERE ..complex statement..;
COMMIT;

The problem is: I have to execute these statements on a new database. It is better to drop and create temporary table, or truncate it only if exists ? How can I deal with this additional table ?

Answer 1

Your temporary table data is visible only in scope of the transaction.

After you commit or rollback the transaction (or disconnect and reconnect), the data vanishes.

You don't need to create the table each time you run the query: just create it once.

TRUNCATE in Oracle is a DDL operation (it commits the transaction it runs under).

Answer 2

Temporary tables do not consume memory in you datafiles. Temporary tables allocate memory of your temp segments. The memory is freed after session or transaction ends.

Truncate on temporary tables is not necessary. Create is once and use it. No sorrows.

More about temporary tables here.