oracle 11G表壓縮

  最近一套生產(chǎn)庫表空間一直告警在90%以上，但的磁盤硬件資源又不足，整個(gè)庫已經(jīng)達(dá)到26T。庫里存儲(chǔ)了近4年的數(shù)據(jù)，與業(yè)務(wù)溝通說歷史數(shù)據(jù)基本上不會(huì)做操作，但是又不能歸檔，所以想到了壓縮表來節(jié)省表空間。

  隨著數(shù)據(jù)庫的增長，我們可以考慮使用oracle的表壓縮技術(shù)。表壓縮可以節(jié)省磁盤空間、減少data buffer cache的內(nèi)存使用量、并可以顯著的提升讀取和查詢的速度。當(dāng)使用壓縮時(shí)，在數(shù)據(jù)導(dǎo)入和DML操作時(shí)，將導(dǎo)致更多的CPU開銷，然而，由于啟用壓縮而減少的I/O需求將抵消CPU的開銷而產(chǎn)生的成本。表的壓縮對(duì)于應(yīng)用程序來說是完全透明的，對(duì)于決策支持系統(tǒng)（DSS）、聯(lián)機(jī)事務(wù)處理系統(tǒng)（OLTP）、歸檔系統(tǒng)（Archive Systems）來說表的壓縮是有益處的。我們可以壓縮表空間，表和分區(qū)。如果壓縮表空間，那么在默認(rèn)的情況下，表空間上創(chuàng)建的所有表都將被壓縮。只有在表執(zhí)行插入、更新或批量數(shù)據(jù)載入時(shí)，才會(huì)執(zhí)行數(shù)據(jù)的壓縮操作。

Table Compression Methods

  當(dāng)使用Basic Compression，warehouse Compression，Archive Compression類型的壓縮時(shí)，會(huì)在發(fā)生批量數(shù)據(jù)導(dǎo)入時(shí)才會(huì)執(zhí)行壓縮。OLTP Compression被用于聯(lián)機(jī)事務(wù)處理系統(tǒng)，可以對(duì)任意的SQL操作執(zhí)行數(shù)據(jù)壓縮。Warehouse Compression和Archive Compression可以獲得很高的壓縮等級(jí)，因?yàn)樗鼈儾捎昧薍ybrid Columnar（混合列）壓縮技術(shù)，Hybrid Columnar采用一種改良的列的存儲(chǔ)形式替代一行為主的存儲(chǔ)形式。Hybird Columnar技術(shù)允許將相同的數(shù)據(jù)存儲(chǔ)在一起，提高了壓縮算法的效率。當(dāng)使用混合列壓縮算法時(shí)，將導(dǎo)致更多的CPU開銷，因此這種壓縮技術(shù)適用于更新不頻繁的數(shù)據(jù)。

Table Compression Characteristics

測(cè)試：

oracle版本11.2.0.4

1、創(chuàng)建壓縮表

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、未壓縮表與壓縮表轉(zhuǎn)換

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 ... 語句進(jìn)行壓縮。在這種情況下，壓縮啟用前的記錄不會(huì)被壓縮，只有新插入或更新的數(shù)據(jù)才會(huì)進(jìn)行壓縮。同樣，通過alter table ... nocompres ...語句解除對(duì)一個(gè)表的壓縮，表內(nèi)已壓縮的數(shù)據(jù)還會(huì)繼續(xù)保持壓縮的狀態(tài)，新插入的數(shù)據(jù)就不再被壓縮。

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、分區(qū)表的壓縮

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

--修改分區(qū)的壓縮方式
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

未壓縮的分區(qū)轉(zhuǎn)為壓縮分區(qū)

一個(gè)表可以有壓縮的分區(qū)和未壓縮的分區(qū)，不同的分區(qū)可以使用不同的壓縮方法。可以采用下列的方法改變分區(qū)的壓縮方法：
1、alter table ... modify partition ... compress ... ，該方法僅適用于新插入的數(shù)據(jù)。

2、alter table ... move partition ... compress ... ，該方法適用于新插入的數(shù)據(jù)和已存在的數(shù)據(jù)。

如果要把分區(qū)表轉(zhuǎn)為壓縮表，直接alter table ... move compress ...會(huì)報(bào)錯(cuò)，只能針對(duì)表里的各個(gè)分區(qū)做alter table ... move partition ... compress ...。

表壓縮后對(duì)應(yīng)的索引會(huì)失效，需要重建。

官方文檔：http://docs.oracle.com/cd/E11882_01/server.112/e25494/tables.htm#ADMIN11630

參考文檔：http://blog.itpub.net/29515435/viewspace-1128770/