TEMPORARY - Analytics Database - Teradata Vantage

Specifies the number of bytes to allow by default for creating materialized global temporary tables by users within this database. The number of bytes can be an integer, decimal, or floating point value.

The space used by a global temporary table (table header only) is allocated from the PERM space of the database or user where the global temporary table resides. A CREATE GLOBAL TEMPORARY TABLE request creates table header for the table on each AMP, so the database or user must have adequate PERM space to accommodate the global temporary table header on each AMP. Table header size varies by table definition and the maximum size for a table header can be up to 1 Mbyte.

The space for the materialized temporary table (table header plus possible data row) is allocated from the login user's temporary space. To materialize a temporary table, the user must have adequate temporary space on each AMP to accommodate the materialized temporary table header and the data row (if the AMP owns the row). Note that the user inherits temporary space from its immediate owner if temporary space was not specified when the user was created.

n

Nonnegative integer, decimal, or floating point value. Can use exponential notation (for example, you can write one thousand as 1E3).

Cannot exceed the number of bytes in the TEMPORARY parameter in the profile of the owner. If no temporary space is defined for that profile, then Vantage uses the temporary space limit defined for the individual user-creator.

constant_expression

SQL expression without column references. Must have a nonnegative value that does not exceed the number of bytes in the TEMPORARY parameter in the profile of the owner. If no temporary space is defined for that profile, Vantage uses the temporary space limit defined for the individual user-creator.

Specifying an appropriate constant expression for TEMPORARY enables Vantage 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.

The assigned size does not automatically scale if, for example, you add 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 more than 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.