Oracle Database Compression 1 - Basic Compression

Basic Row Compression

This type of compression is intended for bulk load operations.The database does not compress data modified using conventional DML.You must use direct path loads, ALTER TABLE . . . MOVE operations, or online table redefinition to achieve basic compression. When using basic compression, Oracle automatically set the pctfree to zero. For an uncompressed table/partition, alter table xxx compress basic does nothing to the data, user has to issue: alter table xxx move. Subsequently, user needs to rebuild all indexes on that table.

How it works

Dictionary-based  compression : Table compression works by eliminating column value repetitions within individual blocks. Duplicate values in all the rows and columns in a block are stored once at the beginning of the block, in what is called a symbol table for that block. All occurrences of such values are replaced with a short reference to the symbol table. The compression is higher in blocks that have more repeated values.  Because of the compression mechanism, Oracle does not need to decompress the data in buffer cache, it reconstructs the data in PGA, which is a CPU intensive process.

Compression Factor

The compression factor is defined as the number of blocks necessary to store the information in an uncompressed form divided by the number of blocks necessary for a compressed storage. The compression factor can be estimated by sampling a small number of representative data blocks of the table to be compressed and comparing the average number of records for each block for the uncompressed and compressed case. Experience shows that approximately 1000 data blocks provides a very accurate estimation of the compression factor. Note that the more blocks you are sampling, the more accurate the result become.

To improve the compression factor you must increase the likelihood of value repetitions within a data block. The achievable compression factor depends on the cardinality of a specific column or column pairs (representing the likelihood of column value repetitions) and on the average row length of those columns. Table compression not only compresses duplicate values of a single column but tries to use multi-column value pairs whenever possible. Without a detailed understanding of the data distribution it is very difficult to predict the most optimal order.

You can declare Basic and Advanced compression at the tablespace, table, partition, or subpartition level.Partitions without a specific declaration inherit the attribute from the table definition or, if nothing is specified on table level, from the tablespace definition.

You can also compress any existing uncompressed table partition later on.

Basic Compression and Bitmap Index

To use table compression on partitioned tables with bitmap indexes, you must do the following before you introduce the compression attribute for the first time:

    Mark bitmap indexes unusable.
    Set the compression attribute.
    Rebuild the indexes.

This does not apply to a partitioned table having B-tree indexes only.To avoid the recreation of any bitmap index structure, Oracle recommends creating every partitioned table with at least one compressed partition whenever you plan to partially or fully compress the partitioned table in the future.

Example

create table tt1 compress basic
as
select * from dba_objects where rownum <= 50000;
create table tt2 nocompress  
as
select * from dba_objects where rownum <= 50000;
exec dbms_stats.gather_table_stats(user, 'TT1', cascade=<true);
exec dbms_stats.gather_table_stats(user, 'TT2', cascade=<true);

TABLE_NAME BLOCKS PCT_FREE COMPRESS COMPRESS_FOR
---------------     ---------- ----------      -------- -      -----------
TT1                   201          0     ENABLED   BASIC

TT2                   731         10    DISABLED

Another example shows that compression ratio is largely dependent on level of value repetition.

create table tt1 (
tt1_id number(38) primary key,
tt1_name varchar2(100),
tt1_date date
)
partition by range (tt1_date) (
  partition p2012 values less than (to_date('2013/01/01','yyyy/mm/dd')) nocompress,
  partition p2013 values less than (to_date('2014/01/01','yyyy/mm/dd')) compress basic
)
nologging;
insert /*+ append parallel(2) */ into tt1
select level, to_char('name' || level), to_date('2012/01/01','yyyy/mm/dd')+ mod(level-1,365)
from dual
connect by level< br style='font-size:12px;font-style:normal;font-weight:normal;color:rgb(102, 102, 102);' />commit;
insert /*+ append parallel(2) */ into tt1
select level +500000, to_char('name' || (level+500000)), to_date('2013/01/01','yyyy/mm/dd')+ mod(level-1,365)
from dual
connect by level< br style='font-size:12px;font-style:normal;font-weight:normal;color:rgb(102, 102, 102);' />commit;
exec dbms_stats.gather_table_stats(user,'TT1', cascade=< true);

select partition_name, sum(blocks) from user_tab_partitions
where table_name = 'TT1'
group by partition_name;

PARTITION_NAME                 SUM(BLOCKS)
------------------------------ -----------
P2013                                 1844
P2012                                 2032