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

sessions.

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

COMMIT PRESERVE ROWS and D is ON COMMIT DELETE ROWS. N indicates the object

is not a temporary table.)

To obtain a list of all global temporary tables you own, query the restricted (DBC.TablesVX)

view.

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.