oracle 11G表压缩

发布时间：2020-12-12 15:45:30 所属栏目：百科来源：网络整理

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

Basic compression

High

Minimal

DSS

None.

OLTP compression

High

Minimal

OLTP,DSS

None.

Warehouse compression (Hybrid Columnar Compression)

Higher

DSS

The compression level and CPU overhead depend on compression level specified (LOW or HIGH).

Archive compression (Hybrid Columnar Compression)

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	Compression Level	CPU Overhead	Applications	Notes
最近一套生产库表空间一直告警在90%以上，但的磁盘硬件资源又不足，整个库已经达到26T。库里存储了近4年的数据，与业务沟通说历史数据基本上不会做操作，但是又不能归档，所以想到了压缩表来节省表空间。随着数据库的增长，我们可以考虑使用oracle的表压缩技术。表压缩可以节省磁盘空间、减少data buffer cache的内存使用量、并可以显著的提升读取和查询的速度。当使用压缩时，在数据导入和DML操作时，将导致更多的CPU开销，然而，由于启用压缩而减少的I/O需求将抵消CPU的开销而产生的成本。表的压缩对于应用程序来说是完全透明的，对于决策支持系统（DSS）、联机事务处理系统（OLTP）、归档系统（Archive Systems）来说表的压缩是有益处的。我们可以压缩表空间，表和分区。如果压缩表空间，那么在默认的情况下，表空间上创建的所有表都将被压缩。只有在表执行插入、更新或批量数据载入时，才会执行数据的压缩操作。 Table Compression Methods

最近一套生产库表空间一直告警在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

Table Compression Method

CREATE/ALTER TABLE Syntax

Direct-Path INSERT

Basic compression

COMPRESS [BASIC]

Rows are compressed with basic compression.

COMPRESSandCOMPRESSBASICare equivalent.

Rows inserted without using direct-path insert and updated rows are uncompressed.

OLTP compression

COMPRESS FOR OLTP

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)

COMPRESS FOR QUERY [LOW|HIGH]

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)

COMPRESS FOR ARCHIVE [LOW|HIGH]

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>createtablet_basic(idnumber,namevarchar2(10))compress;

Tablecreated.

zx@ORCL>createtablet_oltp(idnumber,namevarchar2(10))compressforoltp;

Tablecreated.

zx@ORCL>selecttable_name,compression,COMPRESS_FORfromuser_tableswheretable_namein('T_BASIC','T_OLTP');

TABLE_NAME		COMPRESSCOMPRESS_FOR
--------------------------------------------------
T_BASIC		ENABLED	BASIC
T_OLTP			ENABLED	OLTP

2、未压缩表与压缩表转换

2.1 alter table ... compress/nocompress

zx@ORCL>selecttable_name,COMPRESS_FORfromuser_tableswheretable_name='T';

TABLE_NAME		COMPRESSCOMPRESS_FOR
--------------------------------------------------
T			DISABLED

zx@ORCL>altertabletcompress;

Tablealtered.

zx@ORCL>selecttable_name,COMPRESS_FORfromuser_tableswheretable_name='T';

TABLE_NAME		COMPRESSCOMPRESS_FOR
--------------------------------------------------
T			ENABLED	BASIC

zx@ORCL>altertabletnocompress;

Tablealtered.

zx@ORCL>selecttable_name,COMPRESS_FORfromuser_tableswheretable_name='T';

TABLE_NAME		COMPRESSCOMPRESS_FOR
--------------------------------------------------
T			DISABLED

之前未压缩的表可以通过alter table ... compress ... 语句进行压缩。在这种情况下，压缩启用前的记录不会被压缩，只有新插入或更新的数据才会进行压缩。同样，通过alter table ... nocompres ...语句解除对一个表的压缩，表内已压缩的数据还会继续保持压缩的状态，新插入的数据就不再被压缩。

2.2 alter table ... move compress/nocompress

zx@ORCL>selectbytes/1024/1024fromuser_segmentswheresegment_name='T';

BYTES/1024/1024
---------------
	304

zx@ORCL>selecttable_name,COMPRESS_FORfromuser_tableswheretable_name='T';

TABLE_NAME		COMPRESSCOMPRESS_FOR
--------------------------------------------------
T			DISABLED

zx@ORCL>altertabletmovecompress;

Tablealtered.

zx@ORCL>selecttable_name,COMPRESS_FORfromuser_tableswheretable_name='T';

TABLE_NAME		COMPRESSCOMPRESS_FOR
--------------------------------------------------
T			ENABLED	BASIC

zx@ORCL>selectbytes/1024/1024fromuser_segmentswheresegment_name='T';

BYTES/1024/1024
---------------
	72

zx@ORCL>altertabletmovenocompress;

Tablealtered.

zx@ORCL>selecttable_name,COMPRESS_FORfromuser_tableswheretable_name='T';

TABLE_NAME		COMPRESSCOMPRESS_FOR
--------------------------------------------------
T			DISABLED

zx@ORCL>selectbytes/1024/1024fromuser_segmentswheresegment_name='T';

BYTES/1024/1024
---------------
	272

3、分区表的压缩

zx@ORCL>createtablet_comp_part(idnumber,namevarchar2(10))
2partitionbyrange(id)
3(partitionp1valueslessthan(200),4partitionp2valueslessthan(400))
5compress;

Tablecreated.

zx@ORCL>selecttable_name,PARTITION_NAME,COMPRESS_FORfromuser_tab_partitionswheretable_name='T_COMP_PART';

TABLE_NAME		PARTITION_NAME		COMPRESSCOMPRESS_FOR
--------------------------------------------------------------------------------
T_COMP_PART		P1			ENABLEDBASIC
T_COMP_PART		P2			ENABLEDBASIC

--修改分区的压缩方式
zx@ORCL>altertablet_comp_partmodifypartitionp1compressforoltp;

Tablealtered.

zx@ORCL>selecttable_name,COMPRESS_FORfromuser_tab_partitionswheretable_name='T_COMP_PART';

TABLE_NAME		PARTITION_NAME		COMPRESSCOMPRESS_FOR
--------------------------------------------------------------------------------
T_COMP_PART		P1			ENABLEDOLTP
T_COMP_PART		P2			ENABLEDBASIC

未压缩的分区转为压缩分区

一个表可以有压缩的分区和未压缩的分区，不同的分区可以使用不同的压缩方法。可以采用下列的方法改变分区的压缩方法：
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/

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!