The difference between basic table compression and advanced compression,
Excerpt from MOS article difference between Basic Table Compression and Advanced Compression (Doc ID 1548187.1)
Suitable for:
Oracle database-enterprise edition-version 10.2.0.5 and later
Information in this document applies to any platform.
Goal:
What's the difference between Basic table Compression in 10g and advanced table Compression in 11gR2?
Method:
User_tables or Compress_for columns in the Dba_tables view, in versions 11.2 and 11.1, the returned values are different.
In 11.2, the return value of the column is BASIC or OLTP
In 11.1, the return value of the column is DIRECT LOAD only and for all OPERATIONS
Using the ALTER TABLE...MOVE...COMPRESS, the ALTER index...rebuild...compress command, at this point, is used for basic compression, unless you use advanced compression for authorization.
The basic compression is to work at the block level, moving the duplicate bit information on the block to the block header,
and store this information on each block once. Please note that it is not stored once on a block. Such a mechanism allows storage of data twice times, 3 times times, 4 times times, or more, within a block.
Basic compression is only valid in the following cases:
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 is to say that these DML statements produce (result in) Some non-compressed data.
There can be compressed blocks and uncompressed blocks--in a table that is working properly.
There is restrictions as to how can do with a basic compressed table as far as dropping columns and the like.
In the Oracle 11gR1 and update (above) versions, advanced compression option is available.
This option allows normal, conventional path operations these operations to be compressed.
Therefore, a application can save data in a compressed format without using direct path operations.
Restrictions on table Compression table Compression was subject to the following restrictions:
COMPRESS for OLTP and COMPRESS BASIC is not supported for tables with more than 255 columns.
As "COMPRESS for OLTP" are part of the advanced compression, the restriction holds good for it as well.
You need separate license for use 11g advanced Compression.
For more information on the Compression, see the OTN site.
Translated from MoS article the difference between basic table compression and advanced compression in an Oracle database
