DanBrother的部落格

Table Compression Option in Oracle 11gR2

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

* NOCOMPRESS  --> default action for non-compress tables

  COMPRESSION COMPRESS_FOR
  -------------------- --------------------
  DISABLED


* COMPRESS --> same affect as COMPRESS FOR DIRECT_LOAD OPRATIONS ,
                          suitable for Data Warehousing environment

  COMPRESSION COMPRESS_FOR
  -------------------- --------------------
  ENABLED         BASIC

 

* COMPRESS FOR OLTP  --> suitable for OLTP environment, the deprecated name is  
                                             COMPRESS FOR ALL OPERATIONS
                                            prior to 11gR1 for backward compatibility

  COMPRESSION COMPRESS_FOR
  -------------------- --------------------
  ENABLED         OLTP

　

-- Example
for Changing a Non-Compressed table to a Compressed Table 

create table compress_test
(num number(10),
data varchar2(20)
) nocompress nologging;


-- load data
begin
  for i in 1..1000000 loop
     insert /*+parallel(compress_test,4)*/ into compress_test(num,data)
     values(i,'a'||i);
  end loop;
commit;
end;
/

alter table compress_test add constraint
pk_compress_test
primary key(num)
using index tablespace indx nologging;

select table_name,compression,compress_for from
user_tables
where table_name = 'COMPRESS_TEST';

>> 

 
select segment_name,bytes/1024/1024 as MB
from user_segments
where segment_name = 'COMPRESS_TEST';
>> 

 
-- Compress the table by using move compress syntax
alter table compress_test move compress parallel 4;
 

-- The index was marked as UNUSABLE after changing to compress

select index_name,status from user_indexes where
index_name = 'PK_COMPRESS_TEST';

>> 

 
-- Rebuild the unusable index
alter index pk_compress_test rebuild online parallel 4;

select index_name,status from user_indexes where
index_name = 'PK_COMPRESS_TEST';

>> 

-- After the compression, the size of the table is reduced by 2MB

select segment_name,bytes/1024/1024 as MB
from user_segments
where segment_name = 'COMPRESS_TEST';

>> 

 
select table_name,compression,compress_for from user_tables
where table_name = 'COMPRESS_TEST';
>>

 
PS.    Compress on a unique single-column index IS NO GOOD.
         Compress BETTER on a non-unique (data constains duplicated strings) on single or multi-column indexes.