16.20 - Global Temporary Tables - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

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

The following list describes characteristics of global temporary tables:
  • Space usage is charged to the temporary space of the user.

    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 by specifying the LOG option for the CREATE TABLE statement. LOG is the default.
  • You can save the contents of a materialized global temporary table across transactions by specifying ON COMMIT PRESERVE ROWS as the last keywords in the CREATE TABLE statement. The default is not to preserve table contents after a transaction completes (DELETE).
  • The primary index for a global temporary table can be nonpartitioned or row-partitioned. The table can be defined without a primary index. See Partitioned and Nonpartitioned Primary Indexes and Nonpartitioned NoPI Tables.
You cannot specify the following for global temporary tables:
  • Referential integrity constraints
  • Permanent journaling
  • Column partitioning
  • Primary AMP index
  • Hash or join indexes

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 TABLE Global and Temporary.

For information about how block compression works with global temporary tables, see Block-Level Compression and Tables. Also see Teradata Vantage™ - Database Design, B035-1094 and the DBS Control utility in Teradata Vantage™ - Database Utilities , B035-1102 .