15.00 - Global Temporary Tables - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Global Temporary Tables

Global temporary tables have a persistent definition but do not have persistent contents across sessions.

The following list details the general characteristics of global temporary tables.

  • Space usage is charged to the login temporary space of the user.
  • Note that a minimum of 4KB times the number of AMPs on the system of permanent space is also required to contain the table header for each global temporary table.

  • A single session can materialize up to 2,000 global temporary tables at one time.
  • You materialize a global temporary table locally by referencing it in a data manipulation request. To materialize a global temporary table, you must have the appropriate privilege on the base global temporary table or on the containing database or user as required by the request that materializes the table.
  • Any number of different sessions can materialize the same table definition, but the contents are different depending on the DML requests made against the individual materialized tables during the course of a session.
  • You can log global temporary table updates. To do this, specify LOG immediately after the table name in the CREATE TABLE request. LOG is the default.
  • You can save the contents of a materialized global temporary table across transactions. To do this, specify ON COMMIT PRESERVE ROWS as the last keywords in the CREATE TABLE request. The default is not to preserve table contents after a transaction completes (DELETE).
  • The primary index for a global temporary table can be unpartitioned or partitioned (see “Partitioned and Unpartitioned Primary Indexes” on page 603), or the table can be defined with no primary index (see “Unpartitioned NoPI Tables” on page 575).
  • You cannot create a column‑partitioned global temporary table.

    The following options are not permitted for global temporary tables.

  • Referential integrity constraints
  • Permanent journaling
  • You cannot create secondary, hash, or join indexes on a global temporary table.
  • Teradata Database does not check privileges for the materialized instances of global temporary tables because those tables exist only for the duration of the session in which they are materialized.

    See also “CREATE GLOBAL TEMPORARY TRACE TABLE” on page 317.