Oracle表压缩一招鲜
随着大型企业应用软件的持续运行，存放在生产数据库中的中间数据（如订单数据，接口日志等数据）会越来越多，对该数据的归档就显得非常重要。数据归档不仅有利于提高应用的执行效率，而且通过对归档数据的转储可以规避数据库存储的无限增长。
一般对归档数据的处理有两种方式：一种是转储到归档数据库；另外一种是导出数据并刻录到光盘等存储介质上。一般生产中间数据需要保留一定的时间，比如三个月、半年、一年等，由于归档数据一般不会再变化，因而如何减少归档数据占用宝贵的存储空间就显得非常迫切。
在笔者所维护的系统中，由于存储在Oracle数据库中的接口日志表包含了CLOB字段（CLOB字段存储着应用接口的请求和应答XML数据），该表持续6个月会产生1000W多条记录，占用表空间达50G多，并且系统中存在类似的表比较多，导致700多G的表空间经常处在爆满的状态。
笔者发现从Oracle9i开始，Oracle就提供了表级压缩功能，压缩的技术是透明的，用户对压缩表的数据检索操作和普通表无异。Oracle不仅可以对静态表数据进行压缩，还可以对批量插入或加载的数据进行压缩。下面我们分别来看几种表压缩示例及脚本：
（1） 对已经存在数据的普通表的压缩，不包含CLOB，BLOB等大对象字段
ALTER TABLE USER.T_TEST COMPRESS ; //打开表的COMPRESS标记
ALTER TABLE USER.T_TEST MOVE; //对表进行压缩操作
（2） 对使用子查询批量插入的普通表的压缩
使用子查询直接创建压缩表
CREATE TABLE USER.T_TEST COMPRESS AS SELECT * FROM USER.T_NOCOMPRESS;
也可以先创建表，然后打开表的压缩开关，然后进行批量INSERT操作
INSERT /+append / INTO USER.T_TEST SELECT * FROM USER.T_TEST_NOCOMPRESS;
为了提高语句执行速度，也可以给语句增加PARALLEL和NOLOGGING提示
INSERT /+append parallel(USER.T_TEST,8)/INTO USER.T_TEST NOLOGGING SELECT /+parallel (USER.T_TEST_NOCOMPRESS,8)/ * FROM USER.T_TEST_NOCOMPRESS;
（3） 对不包含大对象字段的分区表的压缩
创建带压缩的分区表后，利用（2）中的INSERT SELECT语句，创建压缩分区表示例：
CREATE TABLE T_PAR_BIG_TABLE(OBJECT_ID NUMBER(10),
OBJECT_NAME VARCHAR2(128),
LAST_DDL_TIME DATE)
PARTITION BY RANGE (OBJECT_ID)
(PARTITION p1 VALUES LESS THAN (25155) COMPRESS,
PARTITION p2 VALUES LESS THAN (50310) COMPRESS,
PARTITION p3 VALUES LESS THAN (MAXVALUE) COMPRESS);
对于已有的分区表可以使用下面的语句进行压缩：
ALTER TABLE T_PAR_BIG_TABLE move partition “p1” compress；
为了提高执行效率，同样可以加parallel和nologging提示：
ALTER TABLE T_PAR_BIG_TABLE move partition “p1” compress parallel 8 NOLOGGING;
（4）对包含有二进制大对象表的压缩
上述的压缩只针对普通表，实测的压缩比例为25%左右。如果上述表中包含了CLOB这样的二进制大对象，在表压缩的过程中并不会对二进制大对象字段进行压缩。如果需要对二进制大对象进行压缩，需要按照以下方式先创建压缩表，然后使用INSERT /*+append */ INTO SELECT语句进行压缩：
创建包含二进制大对象的普通表的SQL脚本：
CREATE TABLE T_CLOB_COMPRESS
(MSG_ID NUMBER(11) NOT NULL,
TRANSACTION_ID VARCHAR2(30),
REQ_CONTENT CLOB,
RESP_CONTENT CLOB,
CRT_DATE DATE)
LOB(REQ_CONTENT) STORE AS SECUREFILE REQ_CONTENT (COMPRESS MEDIUM)
LOB(RESP_CONTENT) STORE AS SECUREFILE RESP_CONTENT (COMPRESS MEDIUM)
COMPRESS;
创建包含二进制大对象的分区表的SQL脚本：
CREATE TABLE T_CLOB_PAR_COMPRESS
(MSG_ID NUMBER(11) NOT NULL,
TRANSACTION_ID VARCHAR2(30),
REQ_CONTENT CLOB,
RESP_CONTENT CLOB,
CRT_DATE DATE)
LOB(REQ_CONTENT) STORE AS
SECUREFILE REQ_CONTENT (COMPRESS MEDIUM)
LOB(RESP_CONTENT) STORE AS
SECUREFILE RESP_CONTENT (COMPRESS MEDIUM)
PARTITION BY RANGE(CRT_DATE)
(PARTITION P201408 VALUES
LESS THAN(TO_DATE(‘2014-09-01’,‘YYYY-MM-DD’)) TABLESPACE TBS_TEST COMPRESS,
PARTITION P201409 VALUES
LESS THAN(TO_DATE(‘2014-10-01’,‘YYYY-MM-DD’))
TABLESPACE TBS_TEST COMPRESS);
其中关键字“MEDIUM”表示默认压缩级别，还可以有“HIGH”选项可选，实测两种方式压缩比率差不多。对包含二进制大对象表的压缩实测结果表明，压缩率能达到罕见的90%。由于CLOB中包含的是XML，这么高的压缩比率和XML的关键字重复率很高有直接关系。
Oracle的压缩技术虽好，但不是在所有情况下都适用，压缩只适合不太变动的数据。如果表还存在大量的UPDATE操作，使用Oracle压缩技术会造成表数据的行迁移，导致表数据的更新效率很低。另外，对于已经存在数据的表进行Oracle压缩时，由于使用了MOVE COMPRESS操作，会造成表索引的失效。要解决这个问题，就需要对索引进行REBUILD操作。
Oracle 11G还提供了COMPRESS FOR OLTP开关，这使得数据库系统也能对不带APPEND提示的普通INSERT操作的表数据进行压缩。有实测结果显示， Oracle在写一个BLOCK快满的时候才会对插入的数据进行压缩，这会导致某一个时刻的INSERT变慢，笔者不推荐对生产表进行FOR OLTP的压缩。
另外，如果有对二进制大对象特殊的压缩需求，可以使用JAVA开发语言编写的Oracle的自定义函数，在分别实现压缩和解压缩功能后，就可以在SQL语句中引用定义好的函数进行压缩和解压缩操作。这样做的优点是可以利用第三方的高效压缩算法实现很高的压缩比率，鉴于这种方式涉及到JAVA编程，本文不进行深入讨论。
