We have migrated some tables from another database and unfortunately the tables were not defined with Compression. So we plan to alter the tables accordingly.
Once the table is altered, what is the state of the existing data? Does it get compressed, or is only new data compressed?
If the new data is not compressed, what is the process to compress the data (I hope I don't have to unload and reload).
I understand that indexes need to be re-orded. And once its all done I will then get the space freed up.
SystemAdmin 110000D4XK17917 Posts
Re: Data Comperssion clarification2012-10-10T10:22:47ZThis is the accepted answer. This is the accepted answer.After alter table, data will not be compressed until you perform table reorganization (reorg table).
There is interesting technical article about data compression in the developerWorks library http://www.ibm.com/developerworks/data/library/techarticle/dm-0605ahuja/
Re: Data Comperssion clarification2012-10-10T11:17:12ZThis is the accepted answer. This is the accepted answer.
- bluey01 110000ENQX
jerrys01 100000K8ER104 Posts
Re: Data Comperssion clarification2012-10-10T13:38:51ZThis is the accepted answer. This is the accepted answer.I've seen some discussion in the documentation which talked about needing about 1MB of data to build the internal compression table. Based on this, I'd say that each table with less than 1MB (and maybe even under 10MB) won't see a lot of improvement. There is overhead in doing the compression and uncompression. While this overhead is minimal for large tables (we seen disk and time improvement with both V9.5 & V9.7), it's probably more significant for small tables. Your mileage may vary.
Re: Data Comperssion clarification2012-10-10T22:31:44ZThis is the accepted answer. This is the accepted answer.
- jerrys01 100000K8ER
jmueller 0100001MA671 Posts
Re: Data Comperssion clarification2012-10-15T10:06:33ZThis is the accepted answer. This is the accepted answer.
- jmueller 0100001MA6
Does row compression work on workgroup server edition or do you still have to buy an additional licence - or is it only on enterprise edition?