本文共 6648 字，大约阅读时间需要 22 分钟。
最近一套生产库表空间一直告警在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、创建压缩表
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 | 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
| 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 | 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
| 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 42 43 | 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、分区表的压缩
| 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 | 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/
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1871093，如需转载请自行联系原作者