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.
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:
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 :-)
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.
There are other ways that you can find BLC tables too (if you are on latest 15.10 and above).
There is now a SQL interface (look up for functions that utilize SHOWBLOCK in DBC database).
More importantly, you may also review BLCCompRatio within DBC.StatsV assuming that there are stats on this table.
I am assuming that this table is never updated and there are no net new records.
If this is true, you will see significant increase in CPU while doing such operations on any BLC table.
I would recommend you to look into TDBLC (Temperature based BLC).