Oracle 11gR1 OLTP table压缩初体验

长期以来，压缩一直作为 Oracle 数据库特性以这样或那样的形式存在着。 

压缩始终是非常占用 CPU 的过程，并且需要花费一定时间。通常，如果压缩数据，则数据必须解压缩后才能使用。虽然此要求在数据仓库环境（SQL 通常在大量的行上运行，

漫长的响应时间通常是可以容忍的）中是可以接受的，但在 OLTP 环境中可能无法接受。 

现在，在 Oracle 数据库 11gR1中，可以执行以下命令： 
   ■Enable compression for new tables:
     CREATE TABLE table1 COMPRESS FOR ALL OPERATIONS;

   ■Enable compression on existing table:
     ALTER TABLE table2 COMPRESS FOR ALL OPERATIONS; --This does not trigger compression on existing rows

下面是一个简单的测试
Connected to Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 
Connected as study

创建一个基础数据表
SQL> create table zrp(msdn varchar2(11), no int);
Table created

SQL> begin
2      for i in 100..500 loop
3        for j in 1..100 loop
4          insert into zrp values('1390'||i||'0000',j);
5        end loop;
6      end loop;
7      commit;
8    end;
9 /

PL/SQL procedure successfully completed

再创建3个表,分别是不压缩,直接压缩和oltp 压缩表
create table test_nocompress as select * from zrp;
create table test_compress_direct compress as select * from zrp;
create table test_compress_oltp compress for all operations as select * from zrp where 0=1;

SQL> create table test_nocompress as select * from zrp;
Table created

SQL> create table test_compress_direct compress as select * from zrp;
Table created

SQL> create table test_compress_oltp compress for all operations as select * from zrp where 0=1;
Table created

SQL> select segment_name,bytes from user_segments;

SEGMENT_NAME               BYTES
-------------------------- ----------
ZRP                        1048576
TEST_NOCOMPRESS            1048576
TEST_COMPRESS_DIRECT       589824
TEST_COMPRESS_OLTP         65536

现在我们采用一般的oltp的风格将表zrp中的数据插入到test_compress_oltp

SQL> declare
2      i int:=1;
3    begin
4      for c in (select msdn,no from zrp) loop
5         insert into test_compress_oltp values(c.msdn,c.no);
6         if mod(i,5)=0 then
7            commit;
8         end if;
9         i:=i+1;
10     end loop;
11     commit;
12 end;
13 /

PL/SQL procedure successfully completed

再对比一下他们对存储空间的开销

SQL> select segment_name,bytes from user_segments;

SEGMENT_NAME                BYTES
-------------------------- ----------
ZRP                        1048576
TEST_NOCOMPRESS            1048576
TEST_COMPRESS_DIRECT       589824
TEST_COMPRESS_OLTP         917504

SQL>