Databases grow in sizes over time, there are many reasons for this. Some of them are
1) Detailed web usage (click tracking) capture, to be used for analytics.
2) User created content in web 2.0 applications
3) Rarely used (But needed) historical data that does not get purged (maybe for compliance).
4) Application consolidation.
This means that customers needs to procure and maintain large amounts of disk storage (Read Storage Array Networks, or Network attached storage). I am not a storage expert, but i am sure that the cost of procuring and maintaining highly performant, high end storage (cost per gb per year) is very high.
With the Oracle advanced compression option, customers can reduce the space used by oracle database tables. In oracle 9i oracle introduced the ability to compress data that is bulk loaded into the database (direct path loads and create table as select statements). In oracle 11g this was expanded to compressing all data that is loaded into oracle tables.
The benefits of compression are manyfold.
1) Reduction of disk space used for storage
2) Reduction in I/O bandwidth requirements
3) Faster full table scans
4) Lower server memory usage.
You can enable compression on a table by specifying the “compress for” clause of a create table statement.
eg: create table emp (emp_id number, first_name varchar2(128), last_name varchar2(128) ) compress for all operations.
Oracle enables compression by storing a symbol table of the repeating column values in a block and then, references are stored in the rows to point to the entry in the symbol table.
Oracle compresses a block in batch mode, rather than compressing data every single time a write operation takes place. All inserts into a newly initialized block are uncompressed (So there is no difference here, while insert’s are taking place into this block). When a transaction causes the data in the block to reach the pctfree limit (The algorithm that decides when to re-compress the block, considers various factors, where pctfree is only one of the factors), the contents of the block gets compressed (Note that this is the only transaction that incurs the overhead for compression). Therefore a majority of OLTP transactions on compressed blocks will have exactly the same performance as they would with uncompressed blocks.
The compression ratio that you are going to get varies with the contents of the table. So you should use the compression advisor to determine how much storage you are going to save by using advanced compression on the table.
You can download the compression advisor from http://www.oracle.com/technology/products/database/compression/download.html. You can install it by logging in as SYS to the database and running dbmscomp.sql and prvtcomp.plb.
The compression advisor can be invoked by using a command similar to the one below.
SQL> exec DBMS_COMPRESSION.getratio(‘SH’,’SALES’,10);
Sampling table: SH.SALES
Sampling percentage: 10%
Expected Compression ratio with Advanced Compression Option: 2.96PL/SQL procedure successfully completed.
