Block Level Compression (BLC) is a space reduction technique in Teradata. It has been introduced in 13.10 and is used to apply compression to all the permanent data blocks in a table. The compression rate can be achieved by this technique is 60% on an average.
How BLC works?
Data is stored in the disk array in the form of data blocks and this data blocks are stored in the cylinder. Currently Teradata uses Lempel-Ziv algorithm for block level compression. After applying the BLC, each cylinder will hold more numbers of data blocks in it.
To know about Lempel-Ziv algorithm refer to Lempel-Ziv Compression Technique.
Decompression in BLC
Once the block level is applied in a table, if a query needs to access a single row also, the entire data block needs to be decompressed first. In order to decompress the data block, the compressed version of the data block first will be brought into the FSG cache which is dedicated to the data block, and then decompress into its original version.
One important thing is that this decompressed data block in FSG cache is not sharable across other sessions. That means if some other user wants data from the same data block which is present in FSG cache, needs to decompress the data block again to access data.
Differences from MVC and BLC
High level differences for BLC compare to MVC and ALC are:
- In case of MVC and ALC values need to be define in CREATE TABLE statement, But BLC is activated outside of table definition.
- No need to analyze column value patterns in case of BLC.
- BLC operates on most types of data.
- Potentiality of space reduction is more compare to MVC and ALC.
- Table header size will not increase in case of BLC.
Methods to Apply BLC
As there is overhead associated with compression and decompression steps in terms of CPU usage in BLC, so typically BLC is used to apply on large tables which are accessed and modified less frequently. This kind of data is known as cold data.
BLC can be applied in two ways:
For an empty table query band can be used to apply BLC.
Turn on BLC-
SET QUERY_BAND = ‘BLOCKCOMPRESSION=YES;’ FOR SESSION;
Insert data into empty table-
INSERT INTO STUDENT_MVC AS SELECT * FROM STUDENT;
Turn off BLC–
SET QUERY_BAND = ‘BLOCKCOMPRESSION=NO;’ FOR SESSION;
For a non-empty table, Ferret utility can be used to either compress all the data block in its or to decompress it.