共计 5917 个字符，预计需要花费 15 分钟才能阅读完成。
最近一套生产库表空间一直告警在 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 …。
表压缩后对应的索引会失效，需要重建。
更多 Oracle 相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx?tid=12
本文永久更新链接地址：http://www.linuxidc.com/Linux/2017-01/139277.htm
