Block-compress the data in the table, including compression that occurs based on the temperature of the cylinders on which the data is stored. The definitions of the various thresholds are determined by the DBS Control setting TempBLCThresh. For details, see Utilities, B035-1102.
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 SQL Data Definition Language - Detailed Topics, B035-1184.
- The file system determines the block-level compression setting for the table based on its Teradata Virtual Storage temperature.
- The table uses the block-level compression setting in the DBS Control setting DefaultTableMode. For details, see Utilities, B035-1102.
- 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.
- 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.
Example: Specifying Block-Level Compression
This example creates a table with its BLOCKCOMPRESSION option set to AUTOTEMP, meaning that Teradata Database 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));