Author: Don Burleson

Oracle Compression:

Oracle employs a very nice compression algorithm on database block level which is quite efficient and can yield threefold compression ratio in most cases. The table compression is highly recommended for Data Warehouse environments where the operations tend to be read heavy and batch. Table compression is not recommended for OLTP environments or on tables with high OLTP like operations because of the cost associated with DML operations on compressed tables. This is true with Oracle 10g. Oracle 11g offers OLTP compression as well! Several types of compression introduced by Oracle include

Symbol table:

Compressed blocks contain a structure called a symbol table that maintains compression metadata. The symbol table is stored as another table in the block. Each column in a row in a block references back to an entry in the symbol table in the block

Duplicate values are eliminated by first adding a single copy of the duplicate value to the symbol table when a block is compressed. Each duplicate value is then replaced by a short reference to the appropriate entry in the symbol table.

This makes compressed data self-contained within the database block as the metadata used to translate compressed data into its original state is contained within the block.

Oracle11g compression syntax:

The COMPRESS keyword works for tables, table partitions and entire tablespaces. 

create table fred (col1 number) NOCOMPRESS;
create table fred (col1 number) COMPRESS FOR DIRECT_LOAD OPERATIONS;
create table fred (col1 number) COMPRESS FOR ALL OPERATIONS;

The syntax for creating a compressed tablespace is as under

CREATE TABLESPACE MYSTUFF . . . DEFAULT
{ COMPRESS [ FOR { ALL | DIRECT_LOAD } OPERATIONS ]
| NOCOMPRESS
}

Benefits of Oracle 11g Compression:

This Oracle 11g new inline data compression utility promises below benefits:

Reduced Network Traffic:

Oracle compression makes data blocks compressed/decompressed only within Oracle. As a result the external network packets will be significantly smaller and traffic will be reduced.

Saved Disk Space:

Oracle compression results in huge savings on disk space depending on the nature of your data. 

I/O Speed Up:

Oracle compression enables Oracle shops to boost I/O speeds up to 300x faster than platter disk.  

Reduced Buffer Cache Requirement:

Table compression can significantly reduce disk and buffer cache requirements for database tables.

Reduced Disk Space:

The reduction of disk space using Oracle table compression can be significantly higher than standard compression algorithms, because it is optimized for relational data.

Faster Scan Operations:

Oracle compression results in f aster full scan/range scan operations as tables will reside on less data blocks, full table scans and index range scans can retrieve the rows with less disk I/O. 

Pitfalls of Oracle 11g Compression:

Although Oracle data compression offers huge benefits but the exact overhead costs remain unknown. In a perfect implementation, incoming data would only be decompressed once at read time and the uncompressed copy of the disk block would reside in RAM, thereby minimizing changes to the Oracle kernel code. The overhead on DML must involve below operations.

Hot Blocks:

The possibility of hot blocks on the disk can be increased as data is tightly compressed on the data blocks allowing more rows to be stored. Using large data buffers and/or solid-state disk will alleviate this issue. 

Outbound data block:

The outbound data block must be compressed to fit into its tertiary block size as defined by db_block_size and the tablespace blocksize keyword. 

Data block Unlinking:

Oracle must determine if the data block should be unlinked from the freelist whenever a SQL update, insert of delete changes a data block in RAM. This threshold is defined by the PCTFREE parameter.

Incoming disk blocks:

The incoming disk blocks must be expanded once and stored in the RAM data buffer at physical read time.

RAM Demands:

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Data Warehouse Testing: The key to Data Warehouse success
   A Guideline to Oracle Server Optimization
   Quick Oracle Database Recovery with Minimal Downtime!!
   Oracle Multitable Inserts, Important Concerns!!