oracle advanced compression,Oracle 11g Advanced Compression（下）

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

SQL> truncate table t_compress;

Table truncated

SQL> declare

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

3i integer;

4

5t_infos t_list;

6begin

7select *

8bulk collect into t_infos

9from t_ori;

10

11for i in 1..t_infos.count loop

12insert into T_COMPRESS values t_infos(i);

13

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

15commit;

16end if;

17end loop;

18

19commit;

20end;

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_NAMEEXTENTSBLOCKSMBS

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

T_COMPDIR228967

T_COMPRESS37281622

T_ORI40320025

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 in0.094seconds

SQL> select count(*) from t_compdir;

COUNT(*)

----------

217731

Executed in0.063seconds

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_NAMEEXTENTSBLOCKSMBS

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

T_COMPALL22310248

T_COMPALL40320025

T_ORI40320025

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

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

3i integer;

4

5t_infos t_list;

6begin

7select *

8bulk collect into t_infos

9from t_ori;

10

11for i in 1..t_infos.count loop

12insert into t_compall values t_infos(i);

13

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

15commit;

16end if;

17end loop;

18

19commit;

20end;

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_NAMEEXTENTSBLOCKSMBS

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

T_COMPALL22310248

T_COMPALL40320025

T_ORI40320025

Executed in 0.313 seconds

6、结论

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