Oracle 表壓縮(Table Compression)技術介紹

indexman發表於2015-08-05
原文網址 : https://blog.csdn.net/indexman/article/details/47286237
Oracle

Oracle 表壓縮(Table Compression)介紹

1、官方文件說法：
As your database grows in size, consider using table compression. Compression saves disk space, reduces memory use in the database buffer cache, and can significantly speed query execution during reads. Compression has a cost in CPU overhead for data loading and DML. However, this cost might be offset by reduced I/O requirements

隨著資料庫不斷增長，可以考慮使用表壓縮。壓縮可以節省磁碟空間，減少資料庫buffer cache記憶體使用，並且可以加速查詢。
壓縮對於資料裝載和DML操作有一定的CPU消耗。然而，這些消耗可以為I/O的減少而抵消。

Table compression is completely transparent to applications. It is useful in decision support systems (DSS), online transaction processing (OLTP) systems, and archival systems.

表壓縮對於應用程式完全透明。對於DSS系統、線上事務處理和歸檔系統都很有用處。

You can specify compression for a tablespace, a table, or a partition. If specified at the tablespace level, then all tables created in that tablespace are compressed by default.

你可以為表空間，表或者一個分割槽指定壓縮。如果指定為表空間基本，那麼該表空間所有表建立後預設都啟用壓縮。

Compression can occur while data is being inserted, updated, or bulk loaded into a table. Operations that permit compression include:
壓縮可以再資料插入，更新或者批量裝載入表中時發生。壓縮表允許以下操作：
Single-row or array inserts and updates 單行或多行插入和更新

The following direct-path INSERT methods: 直接路徑插入方法：

Direct path SQL*Loader
1）CREATE TABLE AS SELECT statements
2）Parallel INSERT statements
3）INSERT statements with an APPEND or APPEND_VALUES hint

截止目前，Oracle資料庫共有4種表壓縮技術：
1）Basic compression
2）OLTP compression
3）Warehouse compression (Hybrid Columnar Compression)
4）Archive compression (Hybrid Columnar Compression)

這裡我主要介紹基本壓縮：

2、基本壓縮特點：
1）使用基本壓縮，只有當資料是直接路徑插入或更新記錄（direct-path insert and updated ）時才會發生壓縮。
並且支援有線的資料型別和SQL操作。

3、如何啟用基本壓縮？
1）通過create table語句中指定compress條件。
2）通過alter table .. compress; 來給現有表啟用壓縮；
3）通過alter table .. nocompress; 來禁用表壓縮

4、關於基本壓縮的一些例子
4.1 建立壓縮表 

CREATE TABLE emp_comp compress
AS
SELECT * FROM emp
WHERE 1=2;

4.2 通過資料字典檢視壓縮表狀態

SCOTT@orcl> SELECT table_name, compression, compress_for
  2  FROM user_tables
  3  WHERE table_name='EMP_COMP';

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
EMP_COMP                       ENABLED  BASIC

4.3 通過非直接路徑插入資料

SCOTT@orcl> INSERT INTO emp_comp
  2  SELECT * FROM emp;

已建立16行。

SCOTT@orcl> commit;

--檢視錶佔用
SYS@orcl> exec show_space('EMP_COMP','SCOTT');
Unformatted Blocks  ....................               0
FS1 Blocks (0-25)   ....................               0
FS2 Blocks (25-50)  ....................               0
FS3 Blocks (50-75)  ....................               0
FS4 Blocks (75-100) ....................               5
Full Blocks         ....................               0
Total Blocks ...........................               8
Total Bytes  ...........................          65,536
Total MBytes ...........................               0
Unused Blocks...........................               0
Unused Bytes ...........................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          14,304
Last Used Block.........................               8

--看下emp的佔用
SYS@orcl> exec show_space('EMP','SCOTT');
Unformatted Blocks  ....................               0
FS1 Blocks (0-25)   ....................               0
FS2 Blocks (25-50)  ....................               0
FS3 Blocks (50-75)  ....................               0
FS4 Blocks (75-100) ....................               5
Full Blocks         ....................               0
Total Blocks ...........................               8
Total Bytes  ...........................          65,536
Total MBytes ...........................               0
Unused Blocks...........................               0
Unused Bytes ...........................               0
Last Used Ext FileId....................               4
Last Used Ext BlockId...................             144
Last Used Block.........................               8

--對比與原EMP表的佔用情況，emp_comp表並未壓縮。

注：關於show_space過程的用法，請參考【http://blog.csdn.net/indexman/article/details/47207987】

4.4 通過直接路徑插入資料

drop table emp_comp purge;

CREATE TABLE emp_comp compress
AS
SELECT * FROM emp
WHERE 1=2;


insert /*+ append */ into emp_comp
select *
from emp;

--檢視錶佔用
SYS@orcl> exec show_space('EMP_COMP','SCOTT');
Unformatted Blocks  ....................               0
FS1 Blocks (0-25)   ....................               0
FS2 Blocks (25-50)  ....................               0
FS3 Blocks (50-75)  ....................               0
FS4 Blocks (75-100) ....................               0
Full Blocks         ....................               1
Total Blocks ...........................               8
Total Bytes  ...........................          65,536
Total MBytes ...........................               0
Unused Blocks...........................               4
Unused Bytes ...........................          32,768
Last Used Ext FileId....................               4
Last Used Ext BlockId...................          14,304
Last Used Block.........................               4

--很明顯少佔用4個資料塊

4.5 禁用表壓縮

SCOTT@orcl> alter table emp_comp NOCOMPRESS;

表已更改。

SCOTT@orcl> SELECT table_name, compression, compress_for
  2  FROM user_tables
  3  WHERE table_name='EMP_COMP'
  4  ;

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
EMP_COMP                       DISABLED

4.6 啟用表壓縮

SCOTT@orcl> alter table emp_comp COMPRESS;

表已更改。

SCOTT@orcl> SELECT table_name, compression, compress_for
  2  FROM user_tables
  3  WHERE table_name='EMP_COMP';

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
EMP_COMP                       ENABLED  BASIC

5、最後來看下錶壓縮的幾個使用限制：
1）對於基本壓縮，你無法在壓縮表上增加一個帶預設值的列：

SCOTT@orcl> alter table emp_comp add remark varchar2(200) default 'null';
alter table emp_comp add remark varchar2(200) default 'null'
                         *
第 1 行出現錯誤:
ORA-39726: 不支援對壓縮表執行新增/刪除列操作

2）無法刪除壓縮表上的列：

SCOTT@orcl> alter table emp_comp drop column ename;
alter table emp_comp drop column ename
                                 *
第 1 行出現錯誤:
ORA-39726: 不支援對壓縮表執行新增/刪除列操作

3）表壓縮不支援線上段收縮（Online segment shrink）
4）不支援SecureFiles large objects
5）壓縮表建立時預設設定PCT_FREE 為 0； 除非你手工指定。

相關文章