Notes on – Features of oracle 11 G Compression

Oracle Advanced Compression with Oracle Database 11g

(This is just a short note collected from Oracle : LINK)

OLTP Table Compression

Oracle’s OLTP Table 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. When compared with competing compression algorithms that maintain a global database symbol table, Oracle’s unique approach offers significant performance benefits by not introducing additional I/O when accessing compressed data

Migration and Best Practices

For new tables and partitions, enabling OLTP Table Compression is as easy as simply CREATEing the table or partition and specifying “COMPRESS FOR OLTP”. See the example


 CREATE TABLE emp (emp_id NUMBER, first_name VARCHAR2(128), last_name VARCHAR2(128)) COMPRESS FOR OLTP;

For existing tables and partitions, there are three recommended approaches to enabling OLTP

Table Compressions’ comparison Table:

S. No. Types Compression Remarks
2 Online Redefinition (DBMS_REDEFINITION) future DML and alsocompress existing data
  • For a partitioned Table if you are doing this compress, and at that adding/deletion of a new partition happens #Prob# Indexes will be corrupted and there is a need to rebuild the indexes.
3 ALTER TABLE … MOVE COMPRESS FOR OLTP future DML and alsocompress existing data
  • During the activity activity an exclusive (X) lock is maintained for writing.
  • ALTER TABLE MOVE will invalidate any indexes on the partition or table; those indexes will need to be rebuilt after the ALTER TABLE MOVE.

Check this To Know about topics like

  • Compression for File Data
  • SecureFiles Deduplication
  • SecureFiles Compression
  • Compression for Backup Data
  • Recovery Manager (RMAN) Compression
  • Data Pump Compression
  • Compression for Network Traffic

review: I personally find this could be very useful, if I have to do application in Oracle APEX.


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