oracle 11G表壓縮

  最近一套生產庫表空間一直告警在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 ...。

表壓縮后對應的索引會失效，需要重建。

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

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