16.20 - TEMPORARY - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Syntax and Examples

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-24
dita:mapPath
wkf1512081455740.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval

Disk usage for a materialized global temporary table is charged to the temporary space allocation of the user who referenced the table. Temporary space is reserved prior to spool space for any user defined with this characteristic.

If default temporary space is not defined for a database, then the space allocated for any materialized global temporary tables created in that database is set to the maximum temporary space allocated for its immediate owner.

n
Number of bytes to be allowed by default for creating global temporary tables by users within this database.
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 that redundantly specifies the unit for the amount of space allowed.
SKEW
Keyword that you use to specify a skew limit for TEMPORARY space. You can specify a skew limit percentage that allows the maximum AMP space usage to be above the per-AMP quota, that is, the system maximum space limit divided by the number of AMPs.
constant_expression
Constant expression or constant from 0 through 10000. Specify a value from 1 to 9999 to indicate an AMP-level limit, which is the per-AMP quota * (1+tempskewlimit/100). Specify 0 to set space accounting to the per-AMP level, that is, no skew. A value of 10000 indicates unlimited skew, up to the system maximum space limit.
DEFAULT
Use the value of DBS Control DefaultTempSkewLimitPercent.
PERCENT
Optional keyword that you can include for readability to indicate that the constant_expression or DEFAULT keyword specifies a percentage of allowable skew.

Example: Modifying a Database to Add a Temporary Space Skew Limit

Assume a system with 4 AMPs and a database with 1 gigabyte of temporary space. The per-AMP quota of temporary space is 250 megabytes. If you set the temporary skew limit to 20%, any AMP is allowed a skew limit of 50MB above the per-AMP quota of temporary space. An AMP can use up to 300 megabytes of temporary space as long as total temporary space used does not exceed the 1 gigabyte global limit for temporary space.
    MODIFY DATABASE d1 AS
    PERM = 1e9,
    TEMPORARY = 1e9 SKEW = 20 PERCENT;