1 person found this helpful
Data Archival depends on your business rules and requirements. This answer can be answered by your business or application owner.
In 11g you have some new compression commands, you can use those for read write table. This new feature is for OLTP databases. Conventional compress syntax is same in 9i and 11g so you can use 9i command if you want.
Here my point is: should the 'read-only' tables be the ones that should be compressed or should even the read-write tables be compressed? What I think is that the read-only tables are the ones that are candidate for compression. Can you tell what is the criteria on which to decide what tables should be compressed?
You are using 11g and you can compress read only as well as read-write tables in 11g. Both read only and read write tables are the candidate for compression always. This will save space and could increase the performance but always test it before. This was not an option in 10g. Read the following docs.
I would be careful in mixing compression with updates: an updated row is not compressed again after the change (for basic compression and in many cases also for OLTP compression) and so frequent updates may deny the effects of compression (and lead to row migration). Jonathan Lewis has recently written a great article series on the topic for AllThingsOracle starting with http://allthingsoracle.com/compression-oracle-basic-table-compression/. Another aspect is that compression will need a reorganization of the tables (by ALTER TABLE ... MOVE) since the activation of compression only has an impast on following operations.