ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Oracle 11g Advanced Compression(下)

Oracle 11g Advanced Compression(下)

原创 Linux操作系统 作者:realkid4 时间:2011-09-13 21:59:20 0 删除 编辑

 

下面如果使用一般的分条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虽然可以大幅度减少空间使用,但是对其他操作的影响是存在的。

 

5Compress 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系统各类型复杂操作的高效压缩上,另一方面是体现在对RMANDataPumpDataguard等多范围压缩支持上。

 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/17203031/viewspace-707382/,如需转载,请注明出处,否则将追究法律责任。

下一篇: 再说HWM(上)
请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7753626