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

Below

 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
1 ALTER TABLE … COMPRESS FOR OLTP Only for future DML
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 http://www.oracle.com/technetwork/database/database-technologies/storage/advanced-compression-whitepaper-130502.pdf 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.