[Translated from mos] What is the difference between basic table compression and advanced compression in oracle databases?

[Translated from mos] What is the difference between basic table compression and advanced compression in oracle databases?
Differences between basic table compression and advanced compression,
Extracted from the mos Article Difference Between Basic Table Compression And Advanced Compression (Doc ID 1548187.1)

Applicable:
Oracle Database-Enterprise Edition-Version 10.2.0.5 and later
Information in this document applies to any platform.

Objectives:
What is the difference between Basic Table Compression in 10g and Advanced Table Compression in 11gR2?

Method:
The COMPRESS_FOR column in The user_tables or dba_tables view returns different values in versions 11.2 and 11.1.
In 11.2, the returned value of this column is BASIC or OLTP.
In 11.1, the returned value of this column is direct load only and for all operations.

Use the alter table... move... compress, alter index... rebuild... compress command to perform basic compression unless advanced compression authorization is used.

The basic compression is performed at the block level, and the duplicate bit information on the data block is moved (removed) to the block header,
And store the information on each block once. Note that it is not stored once when a block appears. This mechanism allows you to store 2 times, 3 times, 4 times, or more data in a block.

Basic compression is only effective in the following situations:
Direct path operations -- such as insert/* + APPEND */, alter table t move, create table as select, sqlldr direct = y
Basic compression does not prevent you from using normal nsert/update/delete statements. ---- This means that these dml statements will generate some non-compressed data (result in.
A table can contain compressed blocks and uncompressed blocks. This works properly.
There are restrictions as to what you can do with a basic compressed table as far as dropping columns and the like.

Advanced compression option is available in Oracle 11gR1 and the latest version (above.
This option allows normal and conventional path operations to be compressed.
Therefore, an application can save data in a compressed format without using direct path operations.

Restrictions on Table Compression Table compression is subject to the following restrictions:


Compress for oltp and compress basic are not supported for tables with more than 255 columns.
As "compress for oltp" is part of Advanced compression, the restriction holds good for it as well.

You need separate license to use 11g Advanced Compression.

For more information on Advanced Compression, see the OTN site.