最近一套生产库表空间一直告警在90%以上，但的磁盘硬件资源又不足，整个库已经达到26T。库里存储了近4年的数据，与业务沟通说历史数据基本上不会做操作，但是又不能归档，所以想到了压缩表来节省表空间。
随着数据库的增长，我们可以考虑使用oracle的表压缩技术。表压缩可以节省磁盘空间、减少data buffer cache的内存使用量、并可以显著的提升读取和查询的速度。当使用压缩时，在数据导入和DML操作时，将导致更多的CPU开销，然而，由于启用压缩而减少的I/O需求将抵消CPU的开销而产生的成本。表的压缩对于应用程序来说是完全透明的，对于决策支持系统（DSS）、联机事务处理系统（OLTP）、归档系统（Archive Systems）来说表的压缩是有益处的。我们可以压缩表空间，表和分区。如果压缩表空间，那么在默认的情况下，表空间上创建的所有表都将被压缩。只有在表执行插入、更新或批量数据载入时，才会执行数据的压缩操作。
Table Compression Methods
| Table Compression Method | Compression Level | CPU Overhead | Applications | Notes |
|---|---|---|---|---|
|
Basic compression |
High |
Minimal |
DSS |
None. |
|
OLTP compression |
High |
Minimal |
OLTP, DSS |
None. |
|
Warehouse compression (Hybrid Columnar Compression) |
Higher |
Higher |
DSS |
The compression level and CPU overhead depend on compression level specified (LOW or HIGH). |
|
Archive compression (Hybrid Columnar Compression) |
Highest |
Highest |
Archiving |
The compression level and CPU overhead depend on compression level specified (LOW or HIGH). |
当使用Basic Compression，warehouse Compression，Archive Compression类型的压缩时，会在发生批量数据导入时才会执行压缩。OLTP Compression被用于联机事务处理系统，可以对任意的SQL操作执行数据压缩。Warehouse Compression和Archive Compression可以获得很高的压缩等级，因为它们采用了Hybrid Columnar（混合列）压缩技术，Hybrid Columnar采用一种改良的列的存储形式替代一行为主的存储形式。Hybird Columnar技术允许将相同的数据存储在一起，提高了压缩算法的效率。当使用混合列压缩算法时，将导致更多的CPU开销，因此这种压缩技术适用于更新不频繁的数据。
Table Compression Characteristics
| Table Compression Method | CREATE/ALTER TABLE Syntax | Direct-Path INSERT | Notes |
|---|---|---|---|
|
Basic compression |
|
Rows are compressed with basic compression. |
Rows inserted without using direct-path insert and updated rows are uncompressed. |
|
OLTP compression |
|
Rows are compressed with OLTP compression. |
Rows inserted without using direct-path insert and updated rows are compressed using OLTP compression. |
|
Warehouse compression (Hybrid Columnar Compression) |
|
Rows are compressed with warehouse compression. |
This compression method can result in high CPU overhead. Updated rows and rows inserted without using direct-path insert are stored in row format instead of column format, and thus have a lower compression level. |
|
Archive compression (Hybrid Columnar Compression) |
|
Rows are compressed with archive compression. |
This compression method can result in high CPU overhead. Updated rows and rows inserted without using direct-path insert are stored in row format instead of column format, and thus have a lower compression level. |
测试：
oracle版本11.2.0.4
1、创建压缩表
zx@ORCL>create table t_basic (id number,name varchar2(10)) compress;
Table created.
zx@ORCL>create table t_oltp (id number,name varchar2(10)) compress for oltp;
Table created.
zx@ORCL>select table_name,compression,COMPRESS_FOR from user_tables where table_name in ('T_BASIC','T_OLTP');
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
T_BASIC ENABLED BASIC
T_OLTP ENABLED OLTP
2、未压缩表与压缩表转换
2.1 alter table … compress/nocompress
zx@ORCL>select table_name,compression,COMPRESS_FOR from user_tables where table_name ='T'; TABLE_NAME COMPRESS COMPRESS_FOR ------------------------------ -------- ------------ T DISABLED zx@ORCL>alter table t compress; Table altered. zx@ORCL>select table_name,compression,COMPRESS_FOR from user_tables where table_name ='T'; TABLE_NAME COMPRESS COMPRESS_FOR ------------------------------ -------- ------------ T ENABLED BASIC zx@ORCL>alter table t nocompress; Table altered. zx@ORCL>select table_name,compression,COMPRESS_FOR from user_tables where table_name ='T'; TABLE_NAME COMPRESS COMPRESS_FOR ------------------------------ -------- ------------ T DISABLED
之前未压缩的表可以通过alter table … compress … 语句进行压缩。在这种情况下，压缩启用前的记录不会被压缩，只有新插入或更新的数据才会进行压缩。同样，通过alter table … nocompres …语句解除对一个表的压缩，表内已压缩的数据还会继续保持压缩的状态，新插入的数据就不再被压缩。
2.2 alter table … move compress/nocompress
zx@ORCL>select bytes/1024/1024 from user_segments where segment_name='T'; BYTES/1024/1024 --------------- 304 zx@ORCL>select table_name,compression,COMPRESS_FOR from user_tables where table_name ='T'; TABLE_NAME COMPRESS COMPRESS_FOR ------------------------------ -------- ------------ T DISABLED zx@ORCL>alter table t move compress ; Table altered. zx@ORCL>select table_name,compression,COMPRESS_FOR from user_tables where table_name ='T'; TABLE_NAME COMPRESS COMPRESS_FOR ------------------------------ -------- ------------ T ENABLED BASIC zx@ORCL>select bytes/1024/1024 from user_segments where segment_name='T'; BYTES/1024/1024 --------------- 72 zx@ORCL>alter table t move nocompress; Table altered. zx@ORCL>select table_name,compression,COMPRESS_FOR from user_tables where table_name ='T'; TABLE_NAME COMPRESS COMPRESS_FOR ------------------------------ -------- ------------ T DISABLED zx@ORCL>select bytes/1024/1024 from user_segments where segment_name='T'; BYTES/1024/1024 --------------- 272
3、分区表的压缩
zx@ORCL>create table t_comp_part (id number,name varchar2(10)) 2 partition by range(id) 3 (partition p1 values less than (200), 4 partition p2 values less than (400)) 5 compress; Table created. zx@ORCL>select table_name,PARTITION_NAME,compression,COMPRESS_FOR from user_tab_partitions where table_name = 'T_COMP_PART'; TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR ------------------------------ ------------------------------ -------- ------------ T_COMP_PART P1 ENABLED BASIC T_COMP_PART P2 ENABLED BASIC --修改分区的压缩方式 zx@ORCL>alter table t_comp_part modify partition p1 compress for oltp; Table altered. zx@ORCL>select table_name,PARTITION_NAME,compression,COMPRESS_FOR from user_tab_partitions where table_name = 'T_COMP_PART'; TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR ------------------------------ ------------------------------ -------- ------------ T_COMP_PART P1 ENABLED OLTP T_COMP_PART P2 ENABLED BASIC
未压缩的分区转为压缩分区
一个表可以有压缩的分区和未压缩的分区，不同的分区可以使用不同的压缩方法。可以采用下列的方法改变分区的压缩方法：
1、alter table … modify partition … compress … ，该方法仅适用于新插入的数据。
2、alter table … move partition … compress … ，该方法适用于新插入的数据和已存在的数据。
如果要把分区表转为压缩表，直接alter table … move compress …会报错，只能针对表里的各个分区做alter table … move partition … compress …。
表压缩后对应的索引会失效，需要重建。
官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11630
参考文档：http://blog.itpub.net/29515435/viewspace-1128770/
