I have couple of tables with 2 GB size each and which can grow upto 10 GB each in next 1 yr. DBA has proposed for MVC on certain columns. (TRANSACTION_DATE-- its a partitioned column), EMPLID (UPI Column) and some other columns.
All data in these table are HOT data.
So does MVC really help reporting queries which will run on these tables? Space saving is not the goal here, goal is performace benefit by adding MVC.
Thanks in advance,
No one can say for sure because of all the unknowns associated with your situation and queries, but more rows per block many time results in better query performance. Usually MVC can achieve both space savings and query optimization. I know this is not the case with your situation, but sometimes MVC can be enough of a savings on smaller tables that the optimizer will decide to move the table into memory which of cource can be a performance boost.
Using MVC will help in perfromance here. Basically work that is done for MVC column is 1. while inserting data row it needs to be compressed for column or existing data needs to be compressed.
Data retrieval will be fast as rowsize will be less and memory can hold more rows and data is held in compressed form in most cases.
So there will be less disk I/O.
CPU usage will reduce or will remain same.
MVC is a must, you can save I/O, CPU as well, if you do it well :)
I would recommend PRISE Compress Wizard to implement MVC compression, you can get a full functional free trial here: https://www.prisetools.com/productrequest