blockcompression - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
wgr1555383704548.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1144
lifecycle
previous
Product Category
Teradata Vantage™

Table data is compressed at the block level.

You cannot specify this option to modify the definitions of global temporary tables or volatile tables.

For details, see “CREATE TABLE (Table Options Clause)” in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

AUTOTEMP
The file system determines the block-level compression setting for the table based on its Teradata Virtual Storage temperature. The definitions of the various thresholds are determined by the DBS Control setting TempBLCThresh. For details, see Teradata Vantage™ - Database Utilities , B035-1102 .
DEFAULT
The table uses the block-level compression setting in the DBS Control setting DefaultTableMode. For details, see Teradata Vantage™ - Database Utilities , B035-1102 .
MANUAL
Block level compression is applied based on the default for the table at the time the table is created. Tables can be compressed or uncompressed at any time after loading by using the Ferret COMPRESS and UNCOMPRESS commands. Data inserted into the existing table inherits the current compression status of the table at the time the data is inserted.
ALWAYS
The table and its subtables are always block-level compressed, even if a query band or the applicable DBS Control block-level compression settings indicate otherwise. The DBS Control field BlockLevelCompression must be enabled.
Tables can be compressed at any time after loading by using the Ferret COMPRESS command. This can be useful if this BLOCKCOMPRESSION option was set after the table was already populated.
NEVER
The table and its subtables are not block-level compressed, even if a query band or the applicable DBS Control block compression settings indicate otherwise.

BLOCKCOMPRESSIONALGORITHM

Specifies the algorithm to use for block-level compression (BLC).

This option only applies when the effective BLOCKCOMPRESSION is MANUAL, AUTOTEMP, or ALWAYS.

ZLIB
Block-level compression using the zlib software algorithm.
ELZS_H
Block-level compression using a hardware compression engine board in every system node.
DEFAULT
Uses the setting of the DBS Control field CompressionAlgorithm. See Teradata Vantage™ - Database Utilities , B035-1102 .

BLOCKCOMPRESSIONLEVEL

Specifies a value to indicate a preference for compression speed or compression effectiveness. This option only applies when the BLOCKCOMPRESSIONALGORITHM option is set to ZLIB. Although this option is accepted in combination with BLOCKCOMPRESSIONALGORITHM = ELZS_H, this option is ignored. If BLOCKCOMPRESSIONALGORITHM is altered to ZLIB, this option takes effect.

value
Integer from 1 through 9, where 1 specifies the least processor-intensive compression speed with the lowest compression ratio and 9 specifies the most processor-intensive compression speed with highest compression ratio.
DEFAULT
The table uses the compression level setting of the DBS Control field CompressionLevel. See Teradata Vantage™ - Database Utilities , B035-1102 .