Table Compression Enhancements in Oracle Database 11g Release 1

Table compression was introduced in Oracle 9i as a space saving feature for data warehousing projects. In 11g it is now considered a mainstream feature that is acceptable for OLTP databases. In addition to saving storage space, compression can result in increased I/O performance and reduced memory use in the buffer cache. These advantages do come at a cost, since compression incurs a CPU overhead, so it won't be of benefit to everyone.

The compression clause can be specified at the tablespace, table or partition level with the following options:

NOCOMPRESS - The table or partition is not compressed. This is the default action when no compression clause is specified.

COMPRESS - This option is considered suitable for data warehouse systems. Compression is enabled on the table or partition during direct-path inserts only.

COMPRESS FOR DIRECT_LOAD OPERATIONS - This option has the same affect as the simple COMPRESS keyword.

COMPRESS FOR ALL OPERATIONS - This option is considered suitable for OLTP systems. As the name implies, this option enables compression for all operations, including regular DML statements. This option requires the COMPATIBLE initialization parameter to be set to 11.1.0 or higher. In 11gR2 this option has been renamed to COMPRESS FOR OLTP and the original name has been deprecated.

The following examples show the various compression options applied at table and partition level.

Default compression settings can be specified at the tablespace level using the CREATE TABLESPACE and ALTER TABLESPACE commands. The current settings are displayed in the DEF_TAB_COMPRESSION and COMPRESS_FOR columns of the DBA_TABLESPACES view.