Oracle Database Compression 3 - Hybrid Columnar Compression

Hybrid Columnar Compression

Hybrid Columnar Compression is a feature of the Enterprise Edition of Oracle Database 11g that is dependent on the underlying storage system. Only Exadata storage, ZFS storage, and Pillar Axiom 600 storage support Hybrid Columnar Compression.

With Hybrid Columnar Compression, the database stores the same column for a group of rows together.Storing column data together, with the same data type and similar characteristics, dramatically increases the storage savings achieved from compression. The database compresses data manipulated by any SQL operation, although compression levels are higher for direct path loads. 

If your underlying storage supports Hybrid Columnar Compression, then you can specify the following compression types, depending on your requirements:
    Warehouse compression -  This type of compression is optimized to save storage space, and is intended for data warehouse applications.
    Online archival compression - This type of compression is optimized for maximum compression levels, and is intended for historical data and data that does not change.
To achieve warehouse or online archival compression, you must use direct path loads, ALTER TABLE . . . MOVE operations, or online table redefinition.Hybrid Columnar Compression is optimized for Data Warehousing and decision support applications on Exadata storage. Other Oracle storage systems support Hybrid Columnar Compression, and deliver the same space savings as on Exadata storage, but do not deliver the same level of query performance. 

Compression Unit

Hybrid Columnar Compression uses a logical construct called a compression unit to store a set of rows. When you load data into a table, the database stores groups of rows in columnar format, with the values for each column stored and compressed together. After the database has compressed the column data for a set of rows, the database fits the data into the compression unit.

Hybrid Columnar Compression has implications for row locking (see "Row Locks (TX)"). When an update occurs for a row in an uncompressed data block, only the updated row is locked. In contrast, the database must lock all rows in the compression unit if an update is made to any row in the unit. Updates to rows using Hybrid Columnar Compression cause rowids to change.