Oracle 11g Advanced Compression（下）

 

下面如果使用一般的分條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等多範圍壓縮支援上。