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 in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 . For information about DBS Control fields, see Teradata Vantage™ - Database Utilities , B035-1102 .
- AUTOTEMP
- Block-level compression is applied based on the temperature of the cylinders on which the data is stored. 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 field TempBLCThresh. You can adjust the temperature values for data being loaded using SET QUERY_BAND. See Example: Setting BLOCKCOMPRESSION and TVSTEMPERATURE Query Bands.
- MANUAL
- Block-level compression is applied based on the default for the table at the time the table is created. The defaults for the table are determined by the settings in the Compression group of DBS Control fields. You can override these values using SET QUERY_BAND. See Example: Using the BLOCKCOMPRESSION Reserved Query Band.
- 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.
- NEVER
- The table and its subtables are not block-level compressed, even if a query band or the applicable DBS Control block-level compression settings indicate otherwise.
- DEFAULT
- The table uses the block-level compression setting in the DBS Control field DefaultTableMode.
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.
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.
Example: Specifying Block-Level Compression Set to ALWAYS
This example creates a table that is always block-level compressed using the zlib compression algorithm with a compression level of 8.
CREATE SET TABLE t_blc, NO FALLBACK, NO BEFORE JOURNAL,
NO AFTER JOURNAL, CHECKSUM = DEFAULT,
BLOCKCOMPRESSION = ALWAYS
BLOCKCOMPRESSIONALGORITHM=ZLIB
BLOCKCOMPRESSIONLEVEL=8 (
c1 INTEGER FORMAT '-(10)9' )
UNIQUE PRIMARY INDEX (c1)
PARTITION BY(RANGE_N(c1 BETWEEN 1
AND 500
EACH 5));
Example: Specifying Block-Level Compression Set to AUTOTEMP
This example creates a table with its BLOCKCOMPRESSION option set to AUTOTEMP, so that Vantage can change the compressed state of the data in the table at any time based on its temperature.
CREATE SET TABLE t_blc, NO FALLBACK, NO BEFORE JOURNAL, NO AFTER JOURNAL, CHECKSUM = DEFAULT, BLOCKCOMPRESSION = AUTOTEMP( c1 INTEGER FORMAT '-(10)9' ) UNIQUE PRIMARY INDEX (c1) PARTITION BY(RANGE_N(c1 BETWEEN 1 AND 500 EACH 5));