Software-based BLC is designed for use on large volumes of seldom-accessed data. This is because access requires CPU-intensive decompression. BLC can also achieve relatively good compression on unknown/unclassified data across different column types, including the internal data structures and the row header. Teradata recommends that you do not load or access tables that use software-based BLC during peak hours (except on CPU-rich platforms).
Block-level compression can be enabled at the system level using the DBS Control utility and at the table level using SQL DDL.
Enabling BLC and Temperature-Based BLC at the System Level
To enable BLC for the system, use the DBS Control setting BlockLevelCompression. To enable temperature-based BLC for the system, enable both the BlockLevelCompression and EnableTempBLC settings.
About Temperature-Based Block-Level Compression
You can specify that data is automatically compressed if it is COLD (infrequently accessed) and automatically decompressed when it becomes WARM, HOT, or VERYHOT (more frequently accessed). By default, COLD data is the 20% of the data that is the least often accessed, and HOT and VERYHOT data is the 20% of the data that is the most often accessed. WARM data is anything in between.
Enabling Compression for Individual Tables Using DDL
Enabling block-level compression at the system level allows you to enable block-level compression for individual tables. To accomplish this, use the optional BLOCKCOMPRESSION =block_compression_option clause for the statements CREATE TABLE, ALTER TABLE, CREATE HASH INDEX, and CREATE JOIN INDEX. If you do not use a BLOCKCOMPRESSION clause or choose the DEFAULT keyword, the DBS Control DefaultTableMode is used. The valid specifications for block_compression_option in these statements are:
| DDL Statement BLOCKCOMPRESSION Keywords | Result |
|---|---|
| MANUAL | Table data is not compressed except when:
|
| AUTOTEMP | Table data is compressed or decompressed automatically based on the data temperature. |
| ALWAYS | Data for the table is compressed, even if query band options or DBS Control utility fields indicate otherwise. |
| NEVER | The table is never compressed, even if DBS Control settings, query band options, or Ferret commands indicate otherwise. |
| DEFAULT | The DBS Control tunable DefaultTableMode determines whether the table is manually, automatically, or never compressed. A table set to DEFAULT can be affected by any future change to the system default DBS Control setting. |
Setting the Compression Algorithm for Individual Tables Using DDL
To specify the compression algorithm when you create a table, join index, or hash index or alter a table, use the optional BLOCKCOMPRESSIONALGORITHM option. Possible BLOCKCOMPRESSIONALGORITHM values include the following:
| DDL Statement BLOCKCOMPRESSIONALGORITHM Keywords | Result |
|---|---|
| ZLIB | The ZLIB software algorithm is used to compress data blocks. |
| ELZS_H | The ELZS_H hardware algorithm is used to compress data blocks on systems configured with a hardware board. |
| DEFAULT | The compression algorithm is controlled by the setting of the DBS Control utility field CompressionAlgorithm. |
Setting the Software Compression Level for Individual Tables Using DDL
To specify the software compression level when you create a table, join index, or hash index or alter a table, use the optional BLOCKCOMPRESSIONLEVEL option. Possible BLOCKCOMPRESSIONLEVEL values include the following:
| DDL Statement BLOCKCOMPRESSIONLEVEL Keywords | Result |
|---|---|
| 1-9 | The compression level, where 1 indicates greatest compression speed and 9 indicates greatest compression possible. |
| DEFAULT | The compression level is controlled by the setting of the DBS Control utility field CompressionLevel. |
Restrictions and Usage Notes
The BLOCKCOMPRESSION option applies only to permanent tables (except permanent journal tables).
To apply block-level compression to permanent journals and tables that do not survive restarts, set the DBS Control utility fields that apply to them. See Controlling BLC at the System Level Using the DBS Control Utility.
When you change the value of BLOCKCOMPRESSION, Teradata Database does not change the compression status of data blocks that existed before the change. This means that some data blocks in the table may be uncompressed and some compressed. Changing the value of BLOCKCOMPRESSION affects only new data blocks. To make the table consistent, use the FERRET [UN]COMPRESS commands.
The BLOCKCOMPRESSION option cannot be used with the IMMEDIATE clause.
Compressing Tables, Databases, and Data Types Manually Using Ferret
Use the Ferret utility command COMPRESS to compress:
- Data blocks of an existing table.
- All tables in a database or all data of a particular type in a database: Primary, fallback, primary compressible LOB (JSON, XML, and character LOB), and fallback compressible LOB (JSON, XML, and character LOB). Fallback data includes fallback secondary index subtables.
After a table is compressed, rows added to the table are compressed automatically. To uncompress the data blocks manually, use the Ferret command UNCOMPRESS.
To estimate the effects of the COMPRESS or UNCOMPRESS commands before you run them, use the ESTIMATE option. The output, which is based on sampling your data, shows the estimated block size and CPU usage that will result if you run the command.
Usage Notes
- Teradata does not recommend using the Ferret COMPRESS/UNCOMPRESS commands on a table using temperature-based BLC because if the compressed state of the data does not match its temperature, the system may undo the commands.
- If you use a COMPRESS command on a table specified as BLOCKCOMPRESSION=NEVER, the command is rejected.
- If you use an UNCOMPRESS command on a table specified as BLOCKCOMPRESSION=ALWAYS, the command is rejected.
For details on the Ferret COMPRESS and UNCOMPRESS commands, see .
Compressing Data Loaded into Empty Tables Set to MANUAL
To compress data you are loading into an empty permanent or global temporary table that has the MANUAL compression setting, specify BlockCompression in a SET QUERY_BAND statement:
SET QUERY_BAND = ‘BlockCompression=YES;’ FOR SESSION;
Possible values for BlockCompression, which are applicable to session and transaction query bands, include:
| BlockCompression SET QUERY_BAND Keywords | Result |
|---|---|
| YES | Compress all primary table data, primary compressible LOBs (JSON, XML, and character LOBs), fallback table data, and fallback compressible LOBs (JSON, XML, and character LOBs). |
| NO | Do not compress any new data. |
| ALL | Compress all primary table data, primary compressible LOBs (JSON, XML, and character LOBs), fallback table data, and fallback compressible LOBs (JSON, XML, and character LOBs). Same effect as YES. |
| NONE | Do not compress any data. Same effect as NO. |
| FALLBACK | Compress fallback table data and fallback compressible LOBs (JSON, XML, and character LOBs). You cannot use the NO FALLBACK option and the NO FALLBACK default on platforms optimized for fallback.
|
| ONLYCLOBS | Compress primary and fallback compressible LOBs (JSON, XML, and character LOBs). |
| WITHOUTCLOBS | Compress all data except primary and fallback compressible LOBs (JSON, XML, and character LOBs). |
| FALLBACKANDCLOBS | Compress fallback table data and primary and fallback compressible LOBs (JSON, XML, and character LOBs). |
The BlockCompression query band setting can override these DBS Control settings:
- The default system-level compression (block-level) settings.
- CompressPermPrimaryDBs and CompressPermFallbackDBs.
- CompressGlobalTempPrimaryDBs and CompressGlobalTempFallbackDBs.
- CompressMLoadWorkDBs.
The BlockCompression query band setting cannot override a table-level setting of ALWAYS or a system-level setting of NEVER.