CODEWITHSUNDEEP
oracle-databasetemp-tables
Doomsday
Doomsday

Reputation: 2668

SQL Oracle statement optimized with temporary table

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 ?

Upvotes: 1

Views: 2050

Answers (2)

Christian13467
Christian13467

Reputation: 5604

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.

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425401

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).

Upvotes: 4

Related Questions