15.00 - Block-Level Compression - Teradata Database

Teradata Database Design

prodname
Teradata Database
vrm_release
15.00
category
User Guide
featnum
B035-1094-015K

Block-Level Compression

Block-level compression enables any data block to be stored in compressed form. BLC applies to primary data tables, BLOB, CLOB, and XML subtables, fallback tables, join index subtables, hash index subtables, reference index subtables, journal tables, and even tables that do not survive restarts. Block-level compression is independent of any row compression, multi-value compression, or algorithmic compression applied to the same data. Block-level compression is a lossless method.

The goals of block-level compression are to save storage space and to reduce disk I/O bandwidth. While the number of physical I/O operations might be reduced by BLC, the number of logical I/O operations typically does not change. Block-level compression can use significantly more CPU to compress and decompress data dynamically, so whether query performance is enhanced with block-level compression depends on whether performance is more limited by disk I/O bandwidth or CPU usage.

Block-level compression enables a slight performance gain due to the reduced I/O traffic for hardware platforms that area CPU rich; however, for hardware platforms that are not CPU rich, block-level compression often has a negative performance effect.

Permanent journals and tables that do not survive restarts (spool, redrive, volatile, global temporary, and MultiLoad work) can be compressed at the data block level if the DBS Control setting specific to that table type is enabled (see Utilities: Volume 1 (A-K) for information.

Note: You can apply block-level compression to row‑level security‑protected tables.

The following site provides guidelines for evaluating the impact of block‑level compression on performance and on disk space: http://developer.teradata.com/extensibility/articles/block-level-compression-evaluation-with-the-blc-utility. The site also provides a link to download the Block Level Compression Evaluation utility.

The compression method used by software block-level compression is the public domain algorithm known as zlib (see http://zlib.net for more information).

Block-level compression is controlled by:

  • The Ferret utility
  • DBS Control parameters (see Utilities: Volume 1 (A-K))
  • The BLOCKCOMPRESSION clause of ALTER TABLE, CREATE TABLE, CREATE HASH INDEX, or CREATE JOIN INDEX
  • The BlockCompression reserved query band (for more information, see “Reserved Query Bands for Managing the Block-Level Compression and Storage Temperature of Newly Loaded Data” on page 694 and “SET QUERY_BAND” in SQL Data Definition Language Detailed Topics).
  • In addition to software block-level compression, Teradata Database also offers hardware‑based block-level compression for systems that have installed the required hardware. The advantage of hardware‑based block-level compression is that compression and decompression of data presents very little CPU resource contention. Hardware‑based block-level compression uses the Exar Corporation Lempel‑Ziv‑Stac algorithm.

    For a comprehensive discussion of block-level compression, see Database Administration.