Oracle Database supports multiple types of compression features to reduce storage cost and requirements. Following are the main compression features available in Oracle Database 11g.
Basic Compression: Data is compressed as it‘s loaded into the table. Basic Compression is mainly used for read-only tables and partitions in warehouse environments or inactive data partitions in online transaction processing (OLTP ) environments.
OLTP Table compression: OLTP Table Compression uses an algorithm to eliminate duplicate values within a database block that spans across multiple columns. A structure called a symbol table containing single entry for duplicate value across data block is created in the same compressed block which ends up representing metadata for compression. Each actual duplicate data value will now point to entry in the newly created symbol table in self containing block. OLTP compressed blocks can be read directly without uncompressing the data to improvise IO performance. Oracle only compresses the data in a data block when a threshold limit on block is reached. The “COMPRESS FOR OLTP” command can be used to create new or alter existing tables to use this feature. OLTP compression is suitable for near production data replication, backups and where data duplication is high. It is not supported to tables having more than 255 columns.
File Data De-duplication and Compression: SecureFiles is a feature of Oracle 11g Database used for storing unstructured content, such as documents, spread sheets and XML files. SecureFiles de-duplication feature eliminates duplicate copies of SecureFiles data by storing only one image of the SecureFiles data replacing the duplicate copies with references to single image. File data can also be compressed in addition to de-duplication to save storage. There are three levels of SecureFiles compression available: LOW, MEDIUM, and HIGH.
Backup Data Compression: There are mainly two types of backup data compression, Recovery Manager compression and Data Dump compression.
In Recovery Manager for Advanced Compression (RMAN Compression), uses a database integrated compression feature where backup data is compressed before it is written to storage media. RMAN Compression supports three levels LOW, MEDIUM, and HIGH.
Data Pump Compression enables very fast bulk data and metadata movement between Oracle databases. Oracle Data Pump provides new high-speed, parallel Export and Import utilities (expdp and impdp).
Hybrid Columnar Compression (HCC) technology: Traditionally in databases, all data has been organized within a database block in a ‘row’ format, where all column data for a particular row is stored sequentially within a single database block limiting the storage saving due to different data types. An alternative approach is to store data in a ‘column’ based format, where data is organized and stored by column for data set. Storing column data together increases the storage savings because within a unit of compression, a much higher rate of repeating values is found, and a greater compression ratio can be achieved. But on other hand, it may impact performance when application queries access more than one column, performing updates, or inserting small numbers of rows to database.
Oracle’s Hybrid Columnar Compression technology organizes data within database block by combining of both row and columnar approach for storing data. This hybrid method has the compression benefits of columnar storage, while avoiding the performance degradation of a pure columnar format. To understand in more detail, table’s data is organized into compression units which are larger than typical database block. A compression unit spans multi database blocks where each column is compressed separately and data is organized by column during data load. All column data for a single set of rows is stored in a compression unit.
There are two types of Hybrid Columnar Compression
- Warehouse Compression: This compression gives 10x storage savings and IO reduction. It is more suitable for smaller warehouses where faster performance is needed.
- Archive Compression: It is used for historical data mainly and provided 15x storage saving. It is suitable where space optimization is needed.
Trailing Null Column: In this approach a rows data may contain a column at the end which may or may not have null values. If a trailing column in a row has null value, it’s not stored saving storage space.