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.
For Object File System tables, BLOCKCOMPRESSION has a default setting that you cannot change.
Specifying the maximum DATABLOCKSIZE for a table causes effective compression with the fewest compressed data blocks and the fewest required compression/decompression operations. See 1 MB Data Blocks in Teradata® Orange Book, 541-0010379. Orange Books are located on https://docs.teradata.com/. You must sign in to access the Orange Books.
Block-level compression can cause operations to use considerably more CPU while operating on compressed tables (for example, queries, insert/updates, archive and restores, and the Reconfiguration utility). Unless the system is CPU-rich, these operations impact other workloads and may 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.
Determining How Often Data Is Used
- 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)
Interaction between Block-Level Compression Settings
The table-level setting overrides everything else.