oracle表压缩技术（BAISCvsOLTP） - 数据库编程

oracle压缩技术分为基本表压缩（basic table compression），OLTP表压缩（OLTP table compression），索引压缩（index compression）和混合列压缩（hybrid columnar compression (HCC)）。

basic compression从9i开始推出，是oracle的默认压缩方式。OLTP compression是11g开始推出，支持所有类型的DML操作的数据压缩。压缩会节省磁盘空间，但可能会增加CPU资源的消耗。本文主要讨论常用的basic和LTOP压缩，索引压缩和HCC可以参考oracle其它文档。表压缩技术适合OLAP系统和OLTP系统中数据变化很小的历史表，不适合频繁DML操作的表

1.1 压缩的原理

以OLTP压缩为例，引用参考文档4的说明，原理如下

请看一个 ACCOUNTS 表，它包含以下记录：

在数据库内部，假定一个数据库块包含上述所有行。

解压缩的块看上去是这样的：记录中的所有字段（列）都包含数据。压缩此块时，数据库首先计算在所有行中发现的重复值，将这些值移出行外，然后将其放在块的头部附近。行中的这些重复值将被替换为一个表示其中每个值的符号。从概念上讲，它看上去如下图所示，您可以看到压缩前后的块。

注意这些值是如何从行中取出并放入顶部称为“符号表”的特殊区域中的。列中的每个值都被分配一个符号，此符号将替代行内的实际值。由于符号所占空间小于实际值，因此记录大小也远远小于初始值。行中的重复数据越多，符号表和块越紧凑。

由于压缩作为触发事件发生，而不是在插入行时发生，因此在正常的 DML 进程中压缩对性能没有任何影响。压缩被触发后，对 CPU 的需求肯定会变得很高，但在其他任何时间 CPU 影响都为零，因此压缩也适用于 OLTP 应用程序，这是 Oracle Database 11g 中压缩的平衡点。

除了减少空间占用外，压缩数据还将缩短网络传输时间、减少备份空间，并使在 QA 和测试中维护生产数据库的完整副本变得切实可行。

1.2 basic压缩

下面通过具体的实验来看basic压缩和OLTP压缩的效果和异同点。

basic compression的6组实验，来比较各种情况下的表压缩

sys@MS4ADB3(dtydb5)> select count(*)from test;
 COUNT(*)

----------

    50000

--       1.Baseline CTAS

create table t1 tablespace users
as
select * from test where rownum <=50000;

--       2.CTAS with basic compression enabled

create table t2 compress basic tablespaceusers
as
select * from test where rownum <=50000;

--       3.Normal insert into empty table defined as compressed

create table t3 compress basic tablespaceusers
as
select * from test where rownum = 0;
insert into t3 select * from test whererownum <= 50000;

--       4.Direct path insert into empty table defined as compressed

create table t4 compress basic tablespaceusers
as
select * from test where rownum = 0;

insert /*+append*/ into t4 select * fromtest where rownum <= 50000

--       5.CTAS without compression, then change to compressed

create table t5 tablespace users
as
select * from test where rownum <=50000;

alter table t5 compress basic;

--- 6. table move compress

create table t6 tablespace users
as
select * from test where rownum <=50000;
alter table t6 move compress basic;

对表做表分析

execdbms_stats.gather_table_stats('SYS','T1');

execdbms_stats.gather_table_stats('SYS','T2');

execdbms_stats.gather_table_stats('SYS','T3');

execdbms_stats.gather_table_stats('SYS','T4');

execdbms_stats.gather_table_stats('SYS','T5');

execdbms_stats.gather_table_stats('SYS','T6');

查询表占用空间情况

sys@MS4ADB3(dtydb5)> select  table_name,blocks, pct_free , compression,compress_for
 2      from    user_tables
 3      where   table_name in('T1','T2','T3','T4','T5','T6');

 
TABLE_NAME                                                      BLOCKS   PCT_FREE COMPRESSION      COMPRESS_FOR

---------------------------------------------------------------------- ---------- ---------------- ------------------------
T1                                                                 666         10 DISABLED
T2                                                                 204          0 ENABLED          BASIC
T3                                                                 622          0 ENABLED          BASIC
T4                                                                 204          0 ENABLED          BASIC
T5                                                                  666         10 ENABLED          BASIC
T6                                                                 204          0 ENABLED          BASIC

 

sys@MS4ADB3(dtydb5)> selectsegment_name,bytes/1024 K from dba_segments where segment_name in('T1','T2','T3','T4','T5','T6');

SEGMENT_NA          K

--------- ----------
T1               6144
T2               2048
T3               5120
T4               2048
T5               6144
T6               2048

结果分析：

从上可以看出，

basic compression

在CATS，insert /*+append*/和move compress操作会对数据进行压缩。而alter table compress操作会修改表的压缩属性，但不会对已有数据进行压缩，对压缩表做普通的insert操作也不对对数据进行压缩。压缩表的PCT_FREE为0，说明oracle设计基本压缩表的目的就是认为此类表以后会很少修改

1.3 OLTP压缩

使用OLTP压缩分别做以下6组实验

--    1. Baseline CTAS
create table t21 tablespace users
as
select * from test where rownum <= 50000;

--    2. CTAS with  OLTP compress enabled
create table t22 compress for OLTP tablespace users
as
select * from test where rownum <= 50000;

--    3. Normal insert into empty table defined as compressed
create table t23 compress for OLTP tablespace users
as
select * from test where rownum = 0;

insert into t23 select * from test where rownum <= 50000;

--    4. Direct path insert into empty table defined as compressed
create table t24 compress for OLTP tablespace users
as
select * from test where rownum = 0;

insert /*+append*/ into t24 select * from test where rownum <= 50000;

--    5. CTAS without compression, then change to compressed
create table t25 tablespace users
as
select * from test where rownum <= 50000;

alter table t25 compress for OLTP; 

--- 6. table move compress
create table t26 tablespace users
as
select * from test where rownum <= 50000;
alter table t26 move compress for OLTP;

表分析

exec dbms_stats.gather_table_stats('SYS','T21');
exec dbms_stats.gather_table_stats('SYS','T22');
exec dbms_stats.gather_table_stats('SYS','T23');
exec dbms_stats.gather_table_stats('SYS','T24');
exec dbms_stats.gather_table_stats('SYS','T25');
exec dbms_stats.gather_table_stats('SYS','T26');

表占用空间的大小

sys@MS4ADB3(dtydb5)> select  table_name,blocks, pct_free , compression, compress_for
  2      from    user_tables
  3      where   table_name in ('T21','T22','T23','T24','T25','T26');

TABLE_NAME                                                       BLOCKS   PCT_FREE COMPRESSION      COMPRESS_FOR
-------------

oracle表压缩技术（BAISCvsOLTP）(一)

1.1 压缩的原理

1.2 basic压缩

1.3 OLTP压缩