Thursday, September 12, 2013

More on Global Temporary Tables

Global temporary tables store a query result in a temporary table for use in subsequent
queries. This improves system performance as follows:
The system needs to re-execute the query fewer times.
The user needs to create and drop fewer tables.
Use the CREATE GLOBAL TEMPORARY TABLE statement to create a global table. Global
temporary tables are stored in the Data Dictionary, but their contents do not persist across
Also, their contents are not saved when a transaction completes, unless you explicitly specify
otherwise. To save table rows after a transaction ends, specify ON COMMIT PRESERVE
ROWS as the last keywords in the CREATE statement. However, if you are using a proxy
connection and the connection is lost or switched to another proxy user, all temporary tables
are also lost.

The following options are not available for global temporary tables:
Referential constraints
Permanent journaling
Identity column
Space usage is charged to the temporary space of the login user.

Note: Global temporary tables require both permanent and temporary space. A minimum of
512 bytes of permanent space is required for the table header stored on each AMP for the base
global temporary table definition. Temporary space is required for storing the actual rows.
You can materialize up to 2000 instances of global temporary tables in a single session, as long
as your login user has adequate temporary space.

To obtain a list of all global temporary tables in the system, query the CommitOpt column of
the DBC.TablesV view. For example:

SELECT * FROM DBC.TablesV WHERE CommitOpt IN (‘D’, ‘P’);

The CommitOpt column shows the value D or P for a global temporary table. (P is for ON
is not a temporary table.)
To obtain a list of all global temporary tables you own, query the restricted (DBC.TablesVX)
You can also query the TransLog column to determine if there is transaction logging for a
global temporary table.

You can materialize a global temporary table locally by referencing it in an SQL data
manipulation language (DML) statement. To do this, you must have the appropriate privilege
on the base temporary table or on the containing database, as required by the statement that
materializes the table. Space usage is charged to the temporary space of the login user.

Any number of different sessions can materialize the same table definition, but the contents
change depending on the DML statements applied during the course of a session.
Privileges are not checked on the materialized instances of any global temporary tables

because those tables exist only for the duration of the session in which they are materialized.

No comments:

Post a Comment