下面如果使用一般的分条insert，压缩效果如何呢？

SQL> truncate table t_compress;

Table truncated

SQL> declare

  2    type t_list is table of t_ori%rowtype index by binary_integer;

  3    i integer;

  4 

  5    t_infos t_list;

  6  begin

  7    select *

  8    bulk collect into t_infos

  9    from t_ori;

 10 

 11    for i in 1..t_infos.count loop

 12       insert into T_COMPRESS values t_infos(i);

 13 

 14       if (mod(i,1000)=0) then

 15          commit;

 16       end if;

 17    end loop;

 18 

 19    commit;

 20  end;

 21  /

PL/SQL procedure successfully completed

Executed in 18.703 seconds

SQL> select segment_name, count(*) extents, sum(blocks) blocks,sum(bytes)/1024/1024 MBs from dba_extents where wner='SYS' and segment_name in ('T_ORI','T_COMPRESS','T_COMPDIR') group by segment_name;

SEGMENT_NAME            EXTENTS     BLOCKS        MBS

-------------------- ---------- ---------- ----------

T_COMPDIR                    22        896          7

T_COMPRESS                   37       2816         22

T_ORI                        40       3200         25

Executed in 0.062 seconds

从现象上看，使用append数据表时和普通insert数据表在T_COMPRESS的时候空间差异不大。

下面我们来实验使用compress for direct_load operations的时候，对其他操作影响。

SQL> alter system flush buffer_cache;

System altered

SQL> select count(*) from t_compress;

  COUNT(*)

----------

    217731

Executed in 0.094 seconds

SQL> select count(*) from t_compdir;

  COUNT(*)

----------

    217731

Executed in 0.063 seconds

SQL> select count(*) from t_ori;

  COUNT(*)

----------

    217731

Executed in 0.031 seconds

从查询时间看，普通compress虽然可以大幅度减少空间使用，但是对其他操作的影响是存在的。

5、Compress For All Operation

Compress For All Operation选项是Advanced Compression的重要进步，提供了OLTP类型的压缩操作。

SQL> create table t_compall compress for all operations as select * from t_ori where 1=0;

Table created

Executed in 0.157 seconds

SQL> create table t_compall2 compress for all operations as select * from t_ori;

Table created

Executed in 0.797 seconds

使用Compress for all operations子句就可以建立压缩数据表。

SQL> insert /*+append */into t_compall select * from t_ori;

217731 rows inserted

Executed in 0.797 seconds

SQL> commit;

Commit complete

Executed in 0 seconds

SQL> select segment_name, count(*) extents, sum(blocks) blocks,sum(bytes)/1024/1024 MBs from dba_extents where wner='SYS' and segment_name in ('T_ORI','T_COMPALL','T_COMPALL2') group by segment_name;

SEGMENT_NAME            EXTENTS     BLOCKS        MBS

-------------------- ---------- ---------- ----------

T_COMPALL2                   23       1024          8

T_COMPALL                    40       3200         25

T_ORI                        40       3200         25

Executed in 0.797 seconds

同compress for direct_load operations现象基本类似。Append方式插入表现出一般的压缩比例。

SQL> truncate table t_compall;

Table truncated

Executed in 0.047 seconds

SQL> declare

  2    type t_list is table of t_ori%rowtype index by binary_integer;

  3    i integer;

  4 

  5    t_infos t_list;

  6  begin

  7    select *

  8    bulk collect into t_infos

  9    from t_ori;

 10 

 11    for i in 1..t_infos.count loop

 12       insert into t_compall values t_infos(i);

 13 

 14       if (mod(i,1000)=0) then

 15          commit;

 16       end if;

 17    end loop;

 18 

 19    commit;

 20  end;

 21  /

PL/SQL procedure successfully completed

Executed in 17.687 seconds

SQL> select segment_name, count(*) extents, sum(blocks) blocks,sum(bytes)/1024/1024 MBs from dba_extents where wner='SYS' and segment_name in ('T_ORI','T_COMPALL','T_COMPALL2') group by segment_name;

SEGMENT_NAME            EXTENTS     BLOCKS        MBS

-------------------- ---------- ---------- ----------

T_COMPALL2                   23       1024          8

T_COMPALL                    40       3200         25

T_ORI                        40       3200         25

Executed in 0.313 seconds

6、结论

Oracle 11g推出的Advanced Compression特性是Oracle在数据压缩方面的一个重要里程碑。它突出表现在两个方面的优势：其一是对OLTP系统各类型复杂操作的高效压缩上，另一方面是体现在对RMAN、DataPump和Dataguard等多范围压缩支持上。