16.10 - Global Temporary Tables - Teradata Database

Teradata Database SQL Fundamentals

Product
Teradata Database
Release Number
16.10
Release Date
June 2017
Content Type
Programming Reference
Publication ID
B035-1141-161K
Language
English (United States)

Global temporary tables allow you to define a table template in the database schema, providing large savings for applications that require well known temporary table definitions.

The definition for a global temporary table is persistent and stored in the Data Dictionary. Space usage is charged to login user temporary space. Each user session can materialize as many as 2000 global temporary tables at a time.

How Global Temporary Tables Work

To create the base definition for a global temporary table, use the CREATE TABLE statement and specify the keywords GLOBAL TEMPORARY to describe the table type. Although space usage for materialized global temporary tables is charged to temporary space, creating the global temporary table definition requires an adequate amount of permanent space.

Once created, the table exists only as a definition. It has no rows and no physical instantiation.

When any application in a session accesses a table with the same name as the defined base table, and the table has not already been materialized in that session, then that table is materialized as a real relation using the stored definition. Because that initial invocation is generally due to an INSERT statement, a temporary table—in the strictest sense—is usually populated immediately upon its materialization.

There are only two occasions when an empty global temporary table is materialized:

  • A CREATE INDEX statement is issued on the table.
  • A COLLECT STATISTICS statement is issued on the table.

The following table summarizes this information.

WHEN this statement is issued on a global temporary table that has not yet been materialized … THEN a local instance of the global temporary table is materialized and it is …
INSERT populated with data upon its materialization.
CREATE INDEX ... ON TEMPORARY ...

COLLECT STATISTICS ... ON TEMPORARY ...

not populated with data upon its materialization.
Issuing a SELECT, UPDATE, or DELETE on a global temporary table that is not materialized produces the same result as issuing a SELECT, UPDATE, or DELETE on an empty global temporary table that is materialized.

Example: Using Global Temporary Tables

Suppose there are four sessions, Session 1, Session 2, Session 3, and Session 4 and two users, User_1 and User_2. Consider the scenario in the following two tables.

Step Session Action Result
1 1 The DBA creates a global temporary table definition in the database scheme named globdb.gt1 according to the following CREATE TABLE statement:
CREATE GLOBAL TEMPORARY TABLE globdb.gt1,
LOG
(f1 INT NOT NULL PRIMARY KEY,
f2 DATE,
f3 FLOAT)
ON COMMIT PRESERVE ROWS;
The global temporary table definition is created and stored in the database schema.
2 1 User_1 logs on an SQL session and references globdb.gt1 using the following INSERT statement:
INSERT globdb.gt1 (1, 980101, 11.1);
Session 1 creates a local instance of the global temporary table definition globdb.gt1. This is also referred to as a materialized temporary table.

Immediately upon materialization, the table is populated with a single row having the following values.

f1=1

f2=980101

f3=11.1

This means that the contents of this local instance of the global temporary table definition is not empty when it is created.

From this point on, any INSERT/DELETE/UPDATE statement that references globdb.gt1 in Session 1 maps to this local instance of the table.

3 2 User_2 logs on an SQL session and issues the following SELECT statement.
SELECT * FROM globdb.gt1;
No rows are returned because Session 2 has not yet materialized a local instance of globdb.gt1.
4 2 User_2 issues the following INSERT statement:
INSERT globdb.gt1 (2, 980202, 22.2);
Session 2 creates a local instance of the global temporary table definition globdb.gt1.

The table is populated, immediately upon materialization, with a single row having the following values.

f1=2

f2=980202

f3=22.2

From this point on, any INSERT/DELETE/UPDATE statement that references globdb.gt1 in Session 2 maps to this local instance of the table.

5 2 User_2 logs on again and issues the following SELECT statement:
SELECT * FROM globdb.gt1;
A single row containing the data (2, 980202, 22.2) is returned to the application.
6 1 User_1 logs off from Session 1. The local instance of globdb.gt1 for Session 1 is dropped.
7 2 User_2 logs off from Session 2. The local instance of globdb.gt1 for Session 2 is dropped.

User_1 and User_2 continue their work, logging onto two additional sessions as described in the following table.

Step Session Action Result
1 3 User_1 logs on another SQL session 3 and issues the following SELECT statement:
SELECT * FROM globdb.gt1;
No rows are returned because Session 3 has not yet materialized a local instance of globdb.gt1.
2 3 User_1 issues the following INSERT statement:
INSERT globdb.gt1 (3, 980303, 33.3);
Session 3 creates a local instance of the global temporary table definition globdb.gt1.

The table is populated, immediately upon materialization, with a single row having the following values.

f1=3

f2=980303

f3=33.3

From this point on, any INSERT/DELETE/UPDATE statement that references globdb.gt1 in Session 3 maps to this local instance of the table.

3 3 User_1 again issues the following SELECT statement:
SELECT * FROM globdb.gt1;
A single row containing the data (3, 980303, 33.3) is returned to the application.
4 4 User_2 logs on Session 4 and issues the following CREATE INDEX statement:
CREATE INDEX (f2) ON TEMPORARY globdb.gt1;
An empty local global temporary table named globdb.gt1 is created for Session 4.

This is one of only two cases in which a local instance of a global temporary table is materialized without data.

The other would be a COLLECT STATISTICS statement—in this case, the following statement:

COLLECT STATISTICS ON TEMPORARY globdb.gt1;
5 4 User_2 issues the following SELECT statement:
SELECT * FROM globdb.gt1;
No rows are returned because the local instance of globdb.gt1 for Session 4 is empty.
6 4 User_2 issues the following SHOW TABLE statement:
SHOW TABLE globdb.gt1;
CREATE SET GLOBAL TEMPORARY TABLE globdb.gt1, FALLBACK, LOG
(
f1 INTEGER NOT NULL,
f2 DATE FORMAT 'YYYY-MM-DD',
f3 FLOAT)
UNIQUE PRIMARY INDEX (f1)
ON COMMIT PRESERVE ROWS;
7 4 User_2 issues the following SHOW TEMPORARY TABLE statement:
SHOW TEMPORARY TABLE globdb.gt1;
CREATE SET GLOBAL TEMPORARY TABLE globdb.gt1, FALLBACK, LOG
(
f1 INTEGER NOT NULL,
f2 DATE FORMAT 'YYYY-MM-DD',
f3 FLOAT)
UNIQUE PRIMARY INDEX (f1)
INDEX (f2)
ON COMMIT PRESERVE ROWS;

Note that this report indicates the new index f2 that has been created for the local instance of the temporary table.

With the exception of a few options, materialized temporary tables have the same properties as permanent tables.

After a global temporary table definition is materialized in a session, all further references to the table are made to the materialized table. No additional copies of the base definition are materialized for the session. This global temporary table is defined for exclusive use by the session whose application materialized it.

Materialized global temporary tables differ from permanent tables in the following ways:

  • They are always empty when first materialized.
  • Their contents cannot be shared by another session.
  • The contents can optionally be emptied at the end of each transaction.
  • The materialized table is dropped automatically at the end of each session.

Limitations

You cannot use the following CREATE TABLE options for global temporary tables:

  • PARTITION BYEND
  • WITH DATA
  • Permanent journaling
  • Referential integrity constraints

    This means that a temporary table cannot be the referencing or referenced table in a referential integrity constraint.

References to global temporary tables are not permitted in FastLoad, MultiLoad, or FastExport.

The Table Rebuild utility and the Archive/Recovery utility (with the exception of online archiving)operate on base global temporary tables only. Online archiving does not operate on temporary tables.

Non-ANSI Extensions

Transient journaling options on the global temporary table definition are permitted using the CREATE TABLE statement.

You can modify the transient journaling and ON COMMIT options for base global temporary tables using the ALTER TABLE statement.

Privileges Required

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 statement that materializes the table.

No access logging is performed on materialized global temporary tables, so no access log entries are generated.