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； 除非你手工指定。
