Table compression in Oracle 11g

Compression option is extended in 11g and have following features:

Requires compatibility 11.1 for new functionality
Extended COMPRESS caluse: COMPRESS [BASIC | FOR OLTP]
BASIC – default, bulk-load operations from prior releases
FOR OLTP – it’s new used for OLTP + direct loads
BASIC and FOR OLTP are not supported for tables with more than 255 columns
Only available for heap organized tables
Compression can be different for each partition
COMPRESS [BASIC|FOR OLTP] is not used for BasicFile LOBs
New columns in views DBA/ALL/USER_TABLES and DBA/ALL/USER_TAB_PARTITIONS – COMPRESS, COMPRESS_FOR
Columns can’t be dropped for compressed tables only set as unused

You can encounter as well following syntax:

COMPRESS FOR DIRECT_LOAD OPERATIONS – it’s equal to COMPRESS, COMPRESS BASIC
COMPRESS FOR ALL OPERATIONS – it’s equal to COMPRESS FOR OLTP
NOCOMPRESS – turns off compression

Compression eliminates holes created due to deletions and maximizes contiguous free space.

Turn on compression for new table. Last table is created as not compressed for later tests.

CREATE TABLE tst011014_basic COMPRESS
AS
SELECT * FROM dba_objects;

CREATE TABLE tst011014_oltp COMPRESS FOR OLTP
AS
SELECT * FROM dba_objects;

CREATE TABLE tst011014_nc NOCOMPRESS
AS
SELECT * FROM dba_objects;

Enable compression for existing table

ALTER TABLE tst011014_nc COMPRESS FOR OLTP;

Turn off compression for existing table

ALTER TABLE tst011014_nc NOCOMPRESS;

FOR OLTP algorithm >>>
- Table compression in Oracle 11g -

Compression option is extended in 11g and have following features:

Requires compatibility 11.1 for new functionality
Extended COMPRESS caluse: COMPRESS [BASIC | FOR OLTP]
BASIC – default, bulk-load operations from prior releases
FOR OLTP – it’s new used for OLTP + direct loads
BASIC and FOR OLTP are not supported for tables with more than 255 columns
Only available for heap organized tables
Compression can be different for each partition
COMPRESS [BASIC|FOR OLTP] is not used for BasicFile LOBs
New columns in views DBA/ALL/USER_TABLES and DBA/ALL/USER_TAB_PARTITIONS – COMPRESS, COMPRESS_FOR
Columns can’t be dropped for compressed tables only set as unused

You can encounter as well following syntax:

COMPRESS FOR DIRECT_LOAD OPERATIONS – it’s equal to COMPRESS, COMPRESS BASIC
COMPRESS FOR ALL OPERATIONS – it’s equal to COMPRESS FOR OLTP
NOCOMPRESS – turns off compression

Compression eliminates holes created due to deletions and maximizes contiguous free space.

Turn on compression for new table. Last table is created as not compressed for later tests.

CREATE TABLE tst011014_basic COMPRESS
AS
SELECT * FROM dba_objects;

CREATE TABLE tst011014_oltp COMPRESS FOR OLTP
AS
SELECT * FROM dba_objects;

CREATE TABLE tst011014_nc NOCOMPRESS
AS
SELECT * FROM dba_objects;

Enable compression for existing table

ALTER TABLE tst011014_nc COMPRESS FOR OLTP;

Turn off compression for existing table

ALTER TABLE tst011014_nc NOCOMPRESS;

FOR OLTP algorithm >>>