Oracle 11g表压缩

共计 5917 个字符，预计需要花费 15 分钟才能阅读完成。

最近一套生产库表空间一直告警在 90% 以上，但的磁盘硬件资源又不足，整个库已经达到 26T。库里存储了近 4 年的数据，与业务沟通说历史数据基本上不会做操作，但是又不能归档，所以想到了压缩表来节省表空间。

随着数据库的增长，我们可以考虑使用 Oracle 的表压缩技术。表压缩可以节省磁盘空间、减少 data buffer cache 的内存使用量、并可以显著的提升读取和查询的速度。当使用压缩时，在数据导入和 DML 操作时，将导致更多的 CPU 开销，然而，由于启用压缩而减少的 I / O 需求将抵消 CPU 的开销而产生的成本。表的压缩对于应用程序来说是完全透明的，对于决策支持系统（DSS）、联机事务处理系统（OLTP）、归档系统（Archive Systems）来说表的压缩是有益处的。我们可以压缩表空间，表和分区。如果压缩表空间，那么在默认的情况下，表空间上创建的所有表都将被压缩。只有在表执行插入、更新或批量数据载入时，才会执行数据的压缩操作。

Table Compression Methods

当使用 Basic Compression，warehouse Compression，Archive Compression 类型的压缩时，会在发生批量数据导入时才会执行压缩。OLTP Compression 被用于联机事务处理系统，可以对任意的 SQL 操作执行数据压缩。Warehouse Compression 和 Archive Compression 可以获得很高的压缩等级，因为它们采用了 Hybrid Columnar（混合列）压缩技术，Hybrid Columnar 采用一种改良的列的存储形式替代一行为主的存储形式。Hybird Columnar 技术允许将相同的数据存储在一起，提高了压缩算法的效率。当使用混合列压缩算法时，将导致更多的 CPU 开销，因此这种压缩技术适用于更新不频繁的数据。

Table Compression Characteristics

测试：

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