oracle表压缩技术
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组实验，来比较各种情况下的表压缩
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | 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; |
?
1 2 3 4 5 6 | --- 6. table move compress create table t6 tablespace users as select * from test where rownum <=50000; alter table t6 move compress basic; |
对表做表分析
?
1 2 3 4 5 6 7 8 9 10 11 | 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'); |
查询表占用空间情况
?
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 | 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 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 | -- 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; |
表分析
?
1 2 3 4 5 6 | 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'); |
表占用空间的大小
?
1 2 3 4 5 6 7 8 9 10 11 12 | 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 ------------------------------------------------------------ ---------- ---------- ---------------- ------------------------ T21 666 10 DISABLED T22 225 10 ENABLED OLTP T23 370 10 ENABLED OLTP T24 225 10 ENABLED OLTP T25 666 10 ENABLED OLTP T26 225 10 ENABLED OLTP |
比较分析
OTLP压缩实现了对DML操作的压缩(T23表)，主要原理如图所示，当向空块插入数据时，数据不压缩，只有当数据超过一个阀值时，此时oracle才对数据块进行压缩，而且可能对同一个数据块多次压缩
转化为压缩表的3方法
1. ALTER TABLE … COMPRESS FOR OLTP
此方法对现有数据不压缩，对以后的DML语句相关数据进行OLTP压缩
2. Online Redefinition (DBMS_REDEFINITION)
对现有和以后的数据均压缩。使用DBMS_REDEFINITION可以在线对表进行操作，可以使用并行操作。分区表的global index是个例外，需要在线重定义之后重建索引
3. ALTER TABLE … MOVE COMPRESS FOR OLTP
对现有和以后的数据均压缩。在move过程中，会对表加排它(X)锁，DML操作会被阻塞，可以使用并行提高性能。move操作会导致索引失效，因此move之后需要重建索引。move操作可以改变segment的表空间
分享到：