16.20 - TEMPORARY - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Teradata Database
Teradata Vantage NewSQL Engine
Release Number
March 2019
English (United States)
Last Update

A keyword allowing you to define how much space to allow for creating materialized global temporary tables.

n cannot exceed the temporary space parameter in the profile of the creator.
n can be an integer, a decimal value, or a floating point value.
n refers to bytes, whether or not the optional BYTES keyword is specified.
If no temporary space limit is defined for the profile, the system uses the temporary space limit defined for the individual user. If temporary space is not defined for either the profile or user, the system uses the temporary space limit for the owner of the space in which the user was created.
If both space allocations are defined, temporary space is reserved prior to spool space.
Disk usage for a materialized global temporary table is charged to the temporary space allocation of the user who referenced the table.
Any SQL expression that does not make any column references. Specifying an appropriate constant expression for the TEMPORARY space size of a user enables Teradata Database to assign an optimal quantity of TEMPORARY space that scales to the size of your system by allocating TEMPORARY space on a per AMP basis.
When you specify a TEMPORARY space size based on a constant expression, the assigned size does not automatically scale if, for example, you add more AMPs to your system.
BYTES is an optional keyword that redundantly specifies the unit for the amount of TEMPORARY space
The default is NULL, which causes Teradata Database to use the setting defined for the user.

Example: Using a Constant Expression to Specify the TEMPORARY Space for a Profile

The following statement creates a profile with the amount of TEMPORARY space based on the constant expression 2,000,000 * (HASHAMP() +1). The expression calculates the number of AMPs in the current system and scales the TEMPORARY space for the research_and_development profile to that size.

CREATE PROFILE research_and_development AS 
TEMPORARY = 2000000*(HASHAMP()+1);