Structured Query Language/Temporary Table

Regular tables are containers to store data for a shorter or longer time periods and to make the data available to various processes. In contrast, sometimes, there is the requirement to handle data for a short time and only for local purposes. The provision of TEMPORARY TABLES accomplishes this. They are subject to the SQL syntax in the same way as regular tables.

The common characteristic of all temporary tables is, that every session (connection) gets its own incarnation of the temporary table without any side effect to other sessions. This leads to the situation that every session sees only those data, which it has inserted previously. The data is not shared between different sessions, even if they use the same table name at the same time. Every session works on a different incarnation. A second common characteristic is that with the termination of the session all data of the temporary table is thrown away automatically. An explicit DELETE or DROP TABLE is not necessary.

The concept of temporary tables is similar to the concept of arrays of records within programming languages. The advantage over arrays is the availability of all DML statements known in SQL, eg.: if you need some intermediate data, you can retrieve it from a regular table and store it in a temporary table with one single Insert+Subselect command. The advantage over regular tables is that the overhead of logging and locking might be saved.

There are three slightly different types of temporary tables:

• Global temporary tables
• Local temporary tables
• Declared local temporary tables.

CREATE  GLOBAL TEMPORARY TABLE <table_name> (...) [ ON COMMIT { PRESERVE | DELETE } ROWS ];
CREATE  LOCAL  TEMPORARY TABLE <table_name> (...) [ ON COMMIT { PRESERVE | DELETE } ROWS ];
DECLARE LOCAL  TEMPORARY TABLE <table_name> (...) [ ON COMMIT { PRESERVE | DELETE } ROWS ];

If the phrase 'ON COMMIT DELETE ROWS' is used, the data is automatically thrown away with every COMMIT command, else at the end of the session (or with a DELETE command).

Global Temporary Tables (GTT)

If a GTT is created, its definition becomes part of the database beyond the end of the defining session. Other sessions using the database will see this table definition as part of the schema. GTTs can be defined simultaneously with regular tables. Applications can use the existing GTTs or create their own. Up to this point, GTTs don't differ from regular tables. The distinction relates to the data. As with all temporary tables, every session gets its own incarnation of the table and cannot access data from any other session. If the session terminates, all data from the table is thrown away automatically.

A typical use case is an application that needs a temporary protocol about its own activities like successful actions, exceptions, ... to perform recovery activities later on. This information is not of interest to other sessions. Moreover, it may be deleted at the end of a transaction or at the end of the session.

Another use case is an application that stores an intermediate result set and iterates the set's rows to perform actions depending on the column values.

-- The table may be defined by a different session long time before.
CREATE GLOBAL TEMPORARY TABLE temp1 (
  ts       TIMESTAMP,
  action   CHAR(100),
  state    CHAR(50)
)
ON COMMIT PRESERVE ROWS;
--
-- Insert some data
INSERT INTO temp1 VALUES (current_timestamp, 'node-1-request sended.', 'OK');
INSERT INTO temp1 VALUES (current_timestamp, 'node-2-request sended.', 'OK');
INSERT INTO temp1 VALUES (current_timestamp, 'node-1-answer received.', 'Failed');
INSERT INTO temp1 VALUES (current_timestamp, 'node-2-answer received.', 'OK');
SELECT count(*) FROM temp1 WHERE state = 'OK';
...
COMMIT; 
SELECT count(*) FROM temp1; -- In this example, all rows should have survived the COMMIT command
-- After a disconnect from the database and establishing a new session the table exists and is empty.

Local Temporary Tables (LTT)

The definition of a LTT will never survive the duration of a session. The same applies to its data, which accords to the behavior of all temporary tables. In consequence, every session must define its own LTT before it can store anything into it. Multiple sessions can use the same table name simultaneously without affecting each other, which - again - accords to the behavior of all temporary tables.

-- The table must be defined by the same session (connection), which stores data into it.
CREATE LOCAL TEMPORARY TABLE temp2 (
  ts       TIMESTAMP,
  action   CHAR(100),
  state    CHAR(50)
)
ON COMMIT PRESERVE ROWS;
-- After a disconnect from the database and establishing a new session, the table will not exist.

The SQL-standard distinguishes between SQL-sessions and modules within SQL-sessions. It postulates that LTTs are visible only within the module, which has actually created the table. The tables are not shared between different modules of the same SQL-session. But the LTTs definition occurs in the information schema of the DBMS.

Declared Local Temporary Tables (DLTT)

The main concept of DLTT is very similar to that of LTT. The difference is that as opposed to the definition of an LTT the definition of a DLTT will not occur in the information schema of the DBMS. It is known only by the module where it is defined. You can imagine a DLTT as some kind of a module-local variable.

-- The declaration must be defined by the same module which stores data into the table.
DECLARE LOCAL TEMPORARY TABLE temp3 (
  ts       TIMESTAMP,
  action   CHAR(100),
  state    CHAR(50)
)
ON COMMIT PRESERVE ROWS;
-- After a disconnect from the module and entering the module again, the declaration will not exist.

Implementation Hints

MySQL:

• Omit the keywords LOCAL/GLOBAL and the ON COMMIT phrase. Temporary tables are always LOCAL, and the ON COMMIT acts always in the sense of PRESERVE ROWS.
• GTT and DLTT are not supported.

Oracle:

• LTT and DLTT are not supported.