DWHPro Insights

What is Block Level Compression (BLC) and How Does it Work for Teradata?

By Roland Wenzlofsky ·

What is Block Level Compression (BLC) and How Does it Work for Teradata?

This article includes historical 13.10/14.00 implementation details. Compression controls, algorithms, eligibility and defaults must be checked against the target deployment and release before operational use.

 

What is Block Level Compression (BLC), and how does it work?

Teradata 13.10 introduced Block Level Compression (BLC), a feature that compresses entire data blocks instead of applying compression methods on a column level, such as MVC and ALC.

Compression is applied solely to data blocks containing rows, while the table headers, master index, cylinder index, and write-ahead log (WAL) remain uncompressed. Although primarily intended for permanent tables, block-level compression may also be activated for spool space, temporary space, permanent journal, and workspace utilized for sorting operations.

Applying block-level compression to a table compresses and stores all data blocks on the hard disk or other mass storage devices, reducing disk space.

MVC and BLC act at different levels. Both can reduce storage and I/O, while BLC requires decompression for processing. Evaluate net performance rather than assuming only column-level compression can improve it.

Block Level Compression is not a performance optimization feature.

Compressed blocks require decompression for use. Cache representation and reuse depend on the release and configuration; do not assume that every access repeats disk I/O or that decompressed data can never be reused.

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Concurrent access and updates interact with caching and compression. Measure these costs on the target deployment instead of inferring one decompression per session from the existence of BLC.

BLC applied to Fallback protected tables compresses the primary table and the data blocks associated with the fallback protection sub-table. It is not possible to selectively compress only the primary table or fallback sub-table.

Block Level Compression effectively reduces disk space usage for infrequently accessed tables, though there is an initial one-time cost for compressing all data blocks. Note that CPU overhead for compression and decompression increases for tables with frequent modifications.

The efficiency of Block Level Compression

The table’s data blocks have varying compression rates determined by the demographics of each block’s rows. Teradata leverages the Lempel-Ziv compression algorithm.

Teradata does not compress data blocks that are too small.

When utilizing column-level compression such as MVC or ALC in conjunction with BLC, the advantage gained from BLC is reduced compared to using only BLC. Nevertheless, BLC will continue to contribute to the reduction of disk space utilized by the table.

Subtables for secondary indexes (NUSI and USI) will not undergo block-level compression. It is important to keep this in mind for tables that contain multiple secondary indexes, as the reduction in space may be less than initial expectations. However, any existing join indexes will be compressed by Teradata.

Conditional Block Level Compression

As previously mentioned, BLC should be applied with caution, particularly on CPU-reliant systems as compression and decompression tasks require significant CPU usage.

Teradata 14.00 introduced temperature-based Block Level Compression (BLC) to enhance its usability. This feature can be applied to tables either collectively or separately.

The Teradata Virtual Storage (TVS) System classifies data based on temperature, specifically cold, warm, and hot. This categorization is determined by the frequency of data access. TVS creates a map of cylinders, rather than blocks, to establish the temperature classification. Cold data is rarely accessed, while hot data is the most frequently accessed.

The DBS control setting TempBLCThresh determines which data requires compression, and can be configured as COLD, WARM, or HOT. Each setting corresponds to a specific temperature range.

TempBLCThresh = {COLD|WARM|HOT},0..100

Parameter 1 (COLD|WARM|HOT):
COLD: compress only cold data. TVS sorts all permanent disk space cylinders by temperature and considers the 20% least accessed cylinders cold.
WARM: compress cold and warm blocks
HOT: compress all data blocks (but in this case, you should not use temperature-based BLC)

Parameter 2 (0..100):

This value defines the distinction between compressed and uncompressed data. If set to 30, Teradata will compress only the least accessed 30% of the data. All cold data (20%) and 10% of the warm data will be compressed by Teradata.

When to use Teradata Block Level Compression (BLC)

Compression ratios and CPU costs vary with the algorithm, hardware and data. Compare space, CPU, I/O and elapsed time together.

On a CPU-bound workload, assess the additional compression work against reduced I/O and any hardware acceleration. CPU pressure alone does not establish that BLC should be disabled.

Infrequently accessed large tables are useful compression candidates, but there is no universal 60–70% CPU threshold for choosing BLC.

Need help with query performance or platform costs?

Diagnose plans, skew, spool pressure and expensive workloads. Agree a baseline and acceptance criteria before making changes.

Explore Teradata performance tuning

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Follow DWHPro in Google to see our articles more often in Search.

4 thoughts on “What is Block Level Compression (BLC) and How Does it Work for Teradata?”

Leave a Comment

Teradata Query Performance Tuning: The DWHPro Guide book cover

Get the complete Teradata tuning book, free

Teradata Query Performance Tuning: The DWHPro Guide
By Roland Wenzlofsky & Artemios Vogiatzis

206 pages on statistics and demographics, access paths and indexes, query execution plans, and joins. Get the full PDF and practical Teradata updates from DWHPro.

Confirm your email to receive your download link. Privacy policy. Unsubscribe in any newsletter.

About the free book

Protected by Security by CleanTalk and CleanTalk Anti-Spam