This blog is about Oracle table compression. As we know Oracle offers table compression basic and table compression advanced. Let us compare them.
Licensing: Basic compression is included with Oracle EE license. Advanced compression is $11,500 per CPU. Assume 2x compression ratio, you need 22 TB database before the compression to justify the cost of license for advanced table compression with a one CPU server.
Compression ratio: Both basic and advanced compressions do block level compression. So it can make compression for repeat values both in rows and in columns. General rules for great compression ratio are few distinct values in a block, larger size of values in term of big number or long string, and large block size. Sorted rows will have better compression ratio because few distinct values in a given block. Because some of the data in advanced compression block is not compressed., advanced compression ratio is a little less than the basic compression.
Concurrent access: Basic compression is only effective with bulk insert. Additionally, only one operation can be done at a time with basic compression. Otherwise, Oracle would give “ORA-12838: cannot read/modify an object after modifying it in parallel” error. Advanced compression does not have such restriction.
Performance: Compression uses some CPU resource but needs fewer disks IO. Based on the testing, the performance for insert, delete is relatively good. The update of compressed table is about 10 times slower than the no compressed table and the compressed table grows to the twice the size of no compressed table. The performance for updating with basic compression is worse than that of advanced compression with large number of chained rows.
Calculate the compression ratio: To calculate the compression rate, we count the blocks with rows by following SQL: “Select count(distinct(dbms_rowid.rowid_block_number(rowid))) from a_table;” and a_table is either compressed or not compressed table.
