17.10 - Software-Based Block-Level Compression - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
User Guide
Publication ID
B035-1094-171K
Language
English (United States)

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:
  • You specify a query band option to compress the table.
  • You use the Ferret COMPRESS command.
  • The DBS Control settings enable BLC for specific tables or table types. In this case, the DBS Control setting cannot be NEVER.
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, Vantage 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).

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.
  • Secondary index primary and fallback subtables are not compressed, regardless of DBSControl Compression settings, table-level attributes, and the BlockCompression query band.

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;
If you set BlockCompression to a value other than the keywords in the following table, the BlockCompression value is not valid and the session still has the default DBS Control settings.

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).
Secondary index primary and fallback subtables are not compressed, regardless of DBSControl Compression settings, table-level attributes, and the BlockCompression query band.
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.