oracle 11G表壓縮

標籤：compress

  最近一套生產庫資料表空間一直警示在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	COMPRESS [BASIC]	Rows are compressed with basic compression.	COMPRESS and COMPRESS BASIC are 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、建立壓縮表

[email protected]>create table t_basic (id number,name varchar2(10)) compress;Table created.[email protected]>create table t_oltp (id number,name varchar2(10)) compress for oltp;Table created.[email protected]>select table_name,compression,COMPRESS_FOR from user_tables where table_name in (‘T_BASIC‘,‘T_OLTP‘);TABLE_NAME       COMPRESS COMPRESS_FOR------------------------------ -------- ------------T_BASIC        ENABLEDBASICT_OLTP       ENABLEDOLTP

2、未壓縮表與壓縮錶轉換

2.1 alter table ... compress/nocompress

[email protected]>select table_name,compression,COMPRESS_FOR from user_tables where table_name =‘T‘;TABLE_NAME       COMPRESS COMPRESS_FOR------------------------------ -------- ------------T       DISABLED[email protected]>alter table t compress;Table altered.[email protected]>select table_name,compression,COMPRESS_FOR from user_tables where table_name =‘T‘;TABLE_NAME       COMPRESS COMPRESS_FOR------------------------------ -------- ------------T       ENABLEDBASIC[email protected]>alter table t nocompress;Table altered.[email protected]>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

[email protected]>select bytes/1024/1024 from user_segments where segment_name=‘T‘;BYTES/1024/1024---------------    304[email protected]>select table_name,compression,COMPRESS_FOR from user_tables where table_name =‘T‘;TABLE_NAME       COMPRESS COMPRESS_FOR------------------------------ -------- ------------T       DISABLED[email protected]>alter table t move compress ;Table altered.[email protected]>select table_name,compression,COMPRESS_FOR from user_tables where table_name =‘T‘;TABLE_NAME       COMPRESS COMPRESS_FOR------------------------------ -------- ------------T       ENABLEDBASIC[email protected]>select bytes/1024/1024 from user_segments where segment_name=‘T‘;BYTES/1024/1024---------------     72[email protected]>alter table t move nocompress;Table altered.[email protected]>select table_name,compression,COMPRESS_FOR from user_tables where table_name =‘T‘;TABLE_NAME       COMPRESS COMPRESS_FOR------------------------------ -------- ------------T       DISABLED[email protected]>select bytes/1024/1024 from user_segments where segment_name=‘T‘;BYTES/1024/1024---------------    272

3、分區表的壓縮

[email protected]>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.[email protected]>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  BASICT_COMP_PART       P2      ENABLED  BASIC--修改分區的壓縮方式[email protected]>alter table t_comp_part modify partition p1 compress for oltp;Table altered.[email protected]>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  OLTPT_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/

本文出自 “DBA Fighting!” 部落格，請務必保留此出處http://hbxztc.blog.51cto.com/1587495/1871093

oracle 11G表壓縮