Structured Query Language/Temporary Table

Regular tables are containers to store data for a shorter or longer time periode and to offer those data to a lot of processes. In contrast, sometimes there is the requirement to handle data for a short time and only for local purposes. This is accomplished by the provision of TEMPORARY TABLES. 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, this definition keeps alive beyond the end of the defining session. Even other session within this schema sees the definition. Therefore the GTT can be defined simultaneous with regular tables and applications don't need to create GTTs by itself (but they can do it). 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 which needs a temporary protocoll about its own activities like successful actions, exceptions, ... to perform recovery activities later on. This information is not of interest for 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 which want to store an intermediate result set and iterate about its single rows to performs actions depending on the columns 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 of 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 behaviour 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 behaviour 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 of a new session the table will not exist.

The SQL-standard distinguishs between SQL-sessions and modules within SQL-sessions. It postulates that LTTs are visible only within that 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 in opposite to the definition of a 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 key words 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.