16.20 - TEMPORARY - Teradata Database - Teradata Vantage NewSQL Engine

The number of bytes to allocate for global temporary table space.

If no temporary space is defined in the profile assigned to a user, Teradata Database uses the setting defined for the individual user. Temporary space is reserved prior to spool space for any user defined with this characteristic. Disk usage for a materialized global temporary table is charged to the temporary space allocation of the user who referenced the table.

The default is NULL, which uses the setting defined for the individual user. Changes to the temporary space allocation in a profile take effect immediately upon submitting the MODIFY PROFILE request.

n

n can be an integer, a decimal value, or a floating point value.

n cannot exceed the temporary space parameter in the profile of the creator. If no temporary space limit is defined for that profile, then Teradata Database uses the temporary space limit defined for the individual user-creator.

n refers to bytes, whether or not the optional BYTES keyword is specified.

constant_expression

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

Optional keyword.

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

This is the original definition of the research_and_development.profile.

CREATE PROFILE research_and_development AS 
DEFAULT DATABASE = it_dev, 
PASSWORD = secret, 
TEMPORARY = 2000000*(HASHAMP()+1); 

The following statement changes the TEMPORARY space for the profile research_and_development to a size based on the constant expression 3,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.

MODIFY PROFILE research_and_development AS 
DEFAULT DATABASE = it_dev, 
PASSWORD = secret, 
TEMPORARY = 3000000*(HASHAMP()+1);