What are the benefits of using Advanced Row Compression?

Advanced Row Compression uses a unique compression algorithm specifically designed to work with OLTP applications. The algorithm works by eliminating duplicate values within a database block, even across multiple columns. Compressed blocks contain a structure called a symbol table that maintains compression metadata. When a block is compressed, duplicate values are eliminated by first adding a single copy of the duplicate value to the symbol table. Each duplicate value is then replaced by a short reference to the appropriate entry in the symbol table. Through this innovative design, compressed data is self-contained within the database block, as the metadata used to translate compressed data into its original state is stored in the block header. When compared with competing compression algorithms that maintain a global database symbol table, Oracle’s approach offers significant performance benefits by not introducing additional I/O when accessing compressed data.The compression ratio achieved in a given environment depends on the data being compressed, specifically the cardinality of the data. In general, organizations can expect to reduce their storage space consumption by a factor of 2x to 4x by using Advanced Row Compression. That is, the amount of space consumed by uncompressed data will be two to four times larger than that of the compressed data. The benefits of Advanced Row Compression go beyond just on-disk storage savings. One significant advantage is Oracle’s ability to read compressed blocks directly without uncompressing the blocks. This helps improve performance due to the reduction in I/O, and the reduction in system calls related to the I/O operations. Further, the buffer cache becomes more efficient by storing more data without having to add memory.


Oracle Database 9i introduced Basic Compression which only compressed data that was loaded using bulk load operations. Advanced Row Compression, a feature of Advanced Compression, allows data to be compressed during all types of data manipulation operations, including conventional DML such as INSERT and UPDATE. In addition, Advanced Row Compression reduces the associated compression overhead of write operations making it suitable for transactional/OLTP environments. Advanced Row Compression, therefore, extends the benefits of compression to all application workloads. Although storage cost savings and optimization across servers (production, development, QA, Test, Backup and etc…) are often seen as the most tangible benefits, additional innovative technologies included in Advanced Compression are designed improve performance and to reduce CapEx and OpEx costs for all components of your IT infrastructure, including memory and network bandwidth as well as heating, cooling and floor-space costs.


I am already using the basic RMAN backup compression feature. In what way is the RMAN backup compression included in Advanced Compression superior to this?

Advanced Compression provides three levels of RMAN Compression: LOW, MEDIUM, and HIGH. The amount of storage savings increases from LOW to HIGH, while potentially consuming more CPU resources. Compression Level LOW provides the fastest compression algorithm and is best suited when backup is constrained by CPU. Compression Level MEDIUM provides a balance between CPU usage and compression ratio and finally, Compression LEVEL HIGH provides the best compression ratio and highest CPU utilization and is best suited when backup is constrained by network or I/O.

What is the performance impact of using Advanced Row Compression?

For DML operations on a compressed table, Advanced Row Compression’s specialized batch algorithm keeps the performance overhead to a minimum. Internal tests at Oracle showed a minimal overhead of less than 5% CPU for a DML workload. It is important to note that Oracle compresses blocks in batch mode rather than compressing data every time a write operation takes place. When a transaction causes the data in the block to reach an internal threshold, all contents of the block are compressed. Subsequently, as more data is added to the block and the threshold is again reached, the entire block is recompressed to achieve the highest level of compression. This process repeats until Oracle determines that the block can no longer benefit from further compression. Only transactions that trigger the compression of the block will experience the slight compression overhead. Therefore, a majority of OLTP transactions on compressed blocks will have the exact same performance as they would with uncompressed blocks.

In today’s Oracle database management systems, physical disk I/O remains one of the foremost bottlenecks. Even at relatively fast speeds of 10 milliseconds, many data intensive Oracle applications can still choke on I/O by having disk enqueues, Oracle block read tasks waiting to pull information from the spinning platters.  Data compression is certainly useful for reducing the amount of physical disk I/O but there are some caveats that need to be followed by the Oracle database administrator.


Oracle 11g compression syntax:

The 11g docs note that the new COMPRESS keyword works for tables, table partitions and entire tablespaces.  Oracle has implemented their data compression at the table level, using new keywords within the “create table” DDL:

1. create table fred (col1 number) NOCOMPRESS;

2. create table fred (col1 number) COMPRESS FOR DIRECT_LOAD OPERATIONS;

3. create table fred (col1 number) COMPRESS FOR ALL OPERATIONS;
4. alter table fred move COMPRESS;




Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s