Using Temporary Tables

A temporary table has a definition and structure like that of a regular table, except that the data only exists for the duration of the current transaction or session. As of Oracle8i, it is possible to create temporary tables “on-the-fly” to hold private session or transaction data. This feature creates the temporary table automatically in the users temporary tablespace.

When a temporary table is created, a dictionary definition of the table is also created. Space is not actually allocated for the table until it is populated, a segment is created in the user’s temporary tablespace. Different sessions using the same GLOBAL TEMPORARY table allocate different temporary segments. The temporary segments are cleaned up automatically at session end or transaction end depending on the identified duration .i.e. (ON COMMIT PRESERVE ROWS or ON COMMIT DELETE ROWS).

Other than the data visibility duration, temporary tables can be used like ordinary tables for most operations.

Indexes can be created for temporary tables but must be done prior to the table being populated with data. DDL operations (with the exception of TRUNCATE) can also be performed on an existing temporary table only if no session is bound to that temporary table at the time.

One point to note when using temporary tables, if a transaction is rolled back, the data entered into the table is lost. In the case of a transaction-specific temporary table, only one transaction can be processed at a time. If there are several autonomous transactions in a single session, each autonomous transaction can use the table only when the previous one commits.

Another point to remember is that because the table is designated as temporary, backup and recovery of a temporary table’s data is not available in the event of a system failure. This is because temporary tables generate no redo log information.

Table constraints can be defined for the table but not for the elements of the table. Constraints can be defined at either the session or transaction level, this is determined by the definition of the table itself.

An example of creating a session-specific temporary table would be as follows:

CREATE GLOBAL TEMPORARY TABLE FUZZY_PINK_SLIPPERS(
PURCHASE_DATE DATE,
SIZE VARCHAR(3),
COST NUMBER(4,2))
ON COMMIT PRESERVE ROWS;

An example of creating a transaction-specific temporary table would be as follows:

CREATE GLOBAL TEMPORARY TABLE FUZZY_BLUE_SLIPPERS(
PURCHASE_DATE DATE,
SIZE VARCHAR(3),
COST NUMBER(4,2))
ON COMMIT DELETE ROWS;

If a primary key or unique key constraint has been defined for a temporary table, it is only applicable to the current session or transaction levels i.e. it is possible for two users to enter values into the same global table from two different sessions with no constraint violations. In the case of a transaction level temporary table, the same values can be entered from transaction-to-transaction.

In addition to indexes and constraints, triggers and views can also be defined in association with these tables.

Drawbacks of Using Temporary Tables

Ultimately, temporary tables can improve performance of complex queries. The performance of conventional queries are relatively slow in comparison because processing may potentially require hitting a table multiple times for each row returned. Efficiency is increased by caching the values of the complex queries into a temporary table, then executing redundant SQL statements against that temporary table.