We use temporary tables just like permanently defined database tables, with several important exceptions and restrictions. Temporary tables persist across transactions in a session and are automatically dropped when a session terminates. Temporary tables have the following operational limitations:
- exist only while a user session is alive
- are not visible to other sessions or transactions
- cannot be altered using the ALTER TABLE, RENAME TABLE, or RENAME COLUMN statements
- do not get backed up
- cannot be used as data providers to views
- cannot be referenced by foreign keys in other tables
- are not displayed by the SHOW command
Temporary tables persist across transactions in a session and are automatically dropped when a session terminates or expires.
Global Temporary Table
A Global Temporary Table (GTT), is a structure that is defined once in the data dictionary. Any number of sessions can insert into and reference their own local copy of the global table structure. Global temporary tables are tables that exist only for the duration of the SQL session in which they are used. The contents of these tables are private to the session, and the system automatically drops the table at the end of that session. However, the system saves the global temporary table definition permanently in the Data Dictionary. The saved definition may be
shared by multiple users and sessions with each session getting its own instance of the table.
CREATE [SET|MULTISET] GLOBAL TEMPORARY TABLE tablename
…table definitions
…column definitions
…index definitions
Volatile Table
A Volatile temp tables (VTT), by way of contrast, are created and used only within the context of a single session, and are never written to the dictionary. If you need a temporary table for a single use only, you can define a volatile table. The definition of a volatile table resides in memory but does not survive across a system restart.
Using volatile tables improves performance even more than using global temporary tables because the system does not store the definitions of volatile tables in the Data Dictionary. Access-rights checking is not necessary because only the creator can access the volatile table.
CREATE [SET|MULTISET] VOALTILE TABLE tablename
…table definitions
…column definitions
…index definitions
ON COMMIT [DELETE|PRESERVE] ROWS
Derived Table
Derived tables are created, used and dropped within a query. These are used to store intermediate results within a query.
SELECT MT.A, MT.B, DT.C
FROM
MAIN_TABLE MT,
(select NEW_table from where D >= 100) DT
where MT.K1=DT.K2;