How you can implement COMPRESSION in Teradata

Compression in Teradata plays a very important role in saving some space and increasing the performance of SQL Query. In Teradata, COMPRESSION can be implemented in three ways:

a) Single Value or Multi Value Compression (MVC)
b) Algorithmic Compression (ALC)
c) Block – Level Compression (BLC)

a) Single Value or Multi Value Compression: This type of compression is widely used in many Teradata Data Warehouse environment. This is easy to implement and can save good amount of space. MVC uses a dictionary to maintain value of data and its corresponding bit pattern. So while saving, Teradata replace the exact value with the bit pattern and save it. Hence, occupying much less space. MVC works at column level and should be defined for each column explicitly for which COMPRESSION is required. Know more about MVC COMPRESSION in TERADATA : MVC Compression in Teradata. To implement this in Teradata, add COMPRESS in table structure along with the column definition to which you want to compress:

CREATE TABLE EMPLOYEES
(
EMP_NAME CHAR(50) COMPRESS ('RAJ','KEVIN','OBAMA'),
EMP_LAST_DATE DATE COMPRESS,
EMP_DEPT CHAR(30) COMPRESS ('HR','IT','FS')
)
PRIMARY INDEX (EMP_NAME);

The problem with MVC is you should know the values which are expected in the columns. Also, a value may be suitable for compression today may not be suitable tomorrow if the data demography of table has changed with time.
b) Algorithmic Compression: This type of compression uses Alogrithm to COMPRESS the data while storing and reverse Algorithm to DECOMPRESS the data while displaying. There are few Algorithms available in TERADATA 13.10 and user can also add custom Algorithms and used it while creating a new table. To implement this in Teradata, add COMPRESS in table structure along with the column definition to which you want to compress:

CREATE TABLE EMPLOYEES
(
EMP_NAME CHAR(50) COMPRESS USING ALGO_NAME DECOMPRESS USING REV_ALGO_NAME,
EMP_LAST_DATE DATE,
EMP_DEPT CHAR(30)
)
PRIMARY INDEX (EMP_NAME);

Using ALC is more resource intensive process. Nowadays we have very high configuration server with very good CPU. We can utilise this strength for ALC and save space. ALC operates at the column level.

c) Block – Level Compression: This type of compression is used to Compress data at block level or table level and not at column level. The cold data or the data which is not accessed frequently is idle for compression using BLC. BLC is very resource intensive process and may take sometime for compression and decompression. However the space saving which can be achieved using this method is phenomenal. We can implement Block Level Compression by adding following statements before loading the table which we need to COMPRESS:

–Turn BLC ON
SET QUERY_BAND = ‘BLOCKCOMPRESSION=YES;’ FOR SESSION;

–Insert into empty table
INSERT INTO EMPLOYEE_BKP AS SELECT * FROM EMPLOYEE;


–Turn BLC OFF
SET QUERY_BAND = ‘BLOCKCOMPRESSION=NO;’ FOR SESSION;

Which of these methods have you used ? Do you think COMPRESSION is the best way of saving some space and also enhancing performance ? Feel free to share your opinion about Compression.