Block-Level Compression Usage Notes | Teradata Vantage - 17.10 - Block-Level Compression Usage Notes - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - Database Design

Advanced SQL Engine
Teradata Database
Release Number
July 2021
English (United States)
Last Update

Teradata recommends that when you are using BLC that you set the CREATE TABLE or ALTER TABLE DATABLOCKSIZE option for each affected table to the maximum setting for your system. Specifying the maximum DATABLOCKSIZE for a table results in effective compression with the fewest compressed data blocks and the fewest required compression/decompression operations. For more information on large data blocks, see 1 MB Data Blocks in Teradata® Orange Book, 541-0010379.

Block-level compression can result in some operations using considerably more CPU while operating on compressed tables (for example, queries, insert/updates, archive and restores, and the Reconfiguration and CheckTable utilities). Unless the system is very CPU rich, these operations will impact other workloads and could lengthen elapsed response times.

Use BLC only for large tables, for example, those tables which, in uncompressed form, are more than 5 times as large as system memory. Although you can use BLC on smaller tables, the CPU cost may outweigh the space benefits, depending on your system load and capability.

BLC can reduce the I/O demand for I/O-intensive DSS queries on compressed tables. This may be useful in situations where CPU is available for the decompression and workload management can keep the I/O intensive DSS queries to an appropriate level of consumption.

To restore a Data Stream Architecture archive made on a source system with hardware-based block-level compression, install the driver package for the hardware compression cards, teradata-expressdx, on the target system. Do this even if the target is not set up for hardware compression, so the target can read the compressed archive.

Determining How Often Data Is Used

To understand how often data is used in tables and other database objects on systems with TVS or Temperature-Based Block-Level Compression, use the Heatmap table function, tdheatmap. You can use this function for the following purposes:
  • Compare the relative temperatures of tables or cylinders over time
  • Determine which tables or cylinders are targeted to the Teradata Intelligent Memory VERYHOT cache
  • Gain information about where data is stored, including media type, storage class, and storage grade (SLOW, MEDIUM, or FAST)

For more information about the Heatmap table function, see Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.