Compression - BLC and MVC

Database
Enthusiast

Compression - BLC and MVC

All,

I see the following disclaimer in the AtanSuite Compression tool.

"Tables using block level compression should not be selected."

Currently we are on appliance which has block level compression automatically enabled. So, do I need to stop worrying about the multi-valued compression? We just moved to 13.10 and I was planning to start the analysis for VARCHAR and revisit the whole MVC compression. Now, I wonder do I ned to do the analysis and implement the MVC or just move on?

We are having great compression ratio via block level compression but I wanted to implement MVC to save some IO. Please advise.

2 REPLIES
Junior Contributor

Re: Compression - BLC and MVC

You will notice that BLC + MVC is not saving much additional diskspace compared to BLC.

But you should still do MVC because BLC data is uncompressed in spool and for backups whereas MVC keeps compressed in both cases.

I once did some comparison and these were the resulting sizes of one of the tables:

uncompressed: 100%

MVC: 63.8%

BLC: 31.0%

MVC + BLC: 28.4%

Other tables showed similar results.

You might ask Atana why they don't recommend it, i suppose it's because they can't predict the actual savings in their compression report anymore :-)

Dieter

Teradata Employee

Re: Compression - BLC and MVC

Hello Dieter,

As this Post concerns BLC, I have a few Questions on BLC:

(a) Is there any way through which I can recognise which Tables have BLC applied. Our DBS Control Settings ensure the BLC Application is subject to Table Level. So, every Table is not implicitly compressed via BLC. My requirement is to find the BackUp & Old Tables with no BLC applied and apply BLC on them.

(b) For Temperature based BLC, only the Data Blocks of eligible Tables in Cold Cylinders are Compressed.

Let's assume a 2-Cylinder based System and 03 Tables (Employee, Dept & Loc). "Employee" has Data Blocks on both Cyl_A and Cyl_B. "Dept" has Blocks on Cyl_A and "Loc" has Blocks on Cyl_C. On Access Count, "Dept" > "Employee" > "Loc". If BLC is only applied on "Employee" and Cyl_A becomes HOT courtesy of heavy usage of "Dept" Table and Cyl_B becomes COLD courtesy of very low usage of "Loc" Table ["Employee" is Medium-Used], then only the Data Blocks of "Employee" on Cyl_B will be compressed via BLC. The same doesn't apply for Data Blocks of "Employee" on Cyl_A.

If my interpretation is correct, whether the Data Blocks of a particular Table are compressed or not depends HEAVILY on the other Tables in the Cylinder. I am aware of generally a Table has Data Blocks across most Cylinders [Parallelism], but the Dependency on other Heavy Users doesn't seem right to me. Kindly let me know your opinion.