In 11g, there is compression for everyone. I am really impressed with the direction of Oracle w.r.t to compression. The main problem with advance compression is that it requires additional options licensing. Not a bad deal if you are trying to save on storage costs; just share the savings with Oracle.
This blog will discuss the key features and implementation of compression. To start with compression is supported at following levels.
- OLTP compression for DML (Structured Data)
- SecureFiles Compression (Unstructured Data)
- Data Guard redo Compression
- RMAN compression
- Data pump compression
- New compression algorithm uses deferred or batched approach
- Data is inserted as is without compression until PCTFREE value is reached.
- Compression of data starts once PCTFREE threshold is reached
- Can be enabled at table, partition or tablespace level
- No need of decompressing the data during reads
- Recommended for low update activity tables
Data guard Compression
- Redo is compressed as it is transmitted over a network.
- Helps efficiently utilize network bandwidth when data guard is across data centers
- Faster resynchronization of Data guard during gap resolution.
- Recommended for low network bandwidth
- Implemented with attribute “COMPRESSION” of initialization parameter LOG_ARCHIVE_DEST_n
- Supports compression of backups using ZLIB algorithm
- Faster compression and low CPU utilization compared to default BZIP2 (10g)
- Low compression ratio compared to BZIP2
- Implement with CONFIGURE COMPRESSION ALGORITHM ‘value’ command where value can be HIGH, MEDIUM(ZLIB) and LOW(LZO).
Data Pump Compression
- Compression of metadata introduced in 10g
- Compression of data introduced in 11g.
- Both are Inline operation
- Save on storage allocation
- No need to uncompress before Import
- Implemented with COMPRESSION attribute, Values supported are ALL,DATA_ONLY,METADATA_ONLY