I just applied this feature (compress for query low) 6.x reduction on one of the table and reduced the size of the table from 48G to 8G.Looks good. You may also want to experiment with QUERY HIGH; I've gotten better compression here without significant performance degradation
Could you please let me know what would be the DML queries (Insert/Update) performance would be here?If rows are updated they would be migrated out of the EHCC-compressed compression unit, so would invovle more work for the database. Inserts without direct path would work just like OLTP compression.
And after inserting the rows, will the data would be in OLTP or NO compression status or in the warehouse compressed mode?OLTP, if not using direct path
Do we need to alter the table before firing Insert/Update/Delete statement on this compressed table?No changes required: both EHCC and OLTP compression are transparent to SQL and applications
As you told that in HCC suppose a table is in warehouse compression (query low) and after insert the new rows are inserted in OLTP compression, then how about the whole table should be compressed in OLTP compression instead so after insert/update whole table would be in same compression type.I'm not sure of the question you are asking here. Can you clarify? I think your question might be answered in the tech white paper above, so see that and follow up if it does not answer it.
Can we move the newly inserted rows from OLTP compression type to Warehouse Compression (query low) using the below command, if a table is already in warehouse compression:This command will recompress all of the table in query low format.
"Alter Table table_name move compress for query low"
ALTER TABLE <TABLE_NAME> MOVE NOCOMPRESSThis command will uncompress all data into a new segment(s), and new data added will also be in uncompressed format.
using the above command we can disable the table compressesion for an existing compressed
table but this method will uncompress only the new inserted data, all data that was already compressed
How can we uncompress all data in a compressed table?
NOTE: this does not change existing data, only new data.
create table foo () compress; insert some data alter table foo compress for query high; direct path insert some data alter table foo nocompress; insert some data