| 壓縮類型 | 支援版本 | 適用場景 | DML 支援 |
|---|---|---|---|
| Basic Table Compression | 9i+ | 數據倉庫、歷史歸檔、唯讀/極少 DML 表 | 僅 Direct-path INSERT/LOAD |
| OLTP Table Compression | 11g+ (Advanced Compression Option) | OLTP 核心表、頻繁 DML 但重複值高的表 | 全支援(INSERT/UPDATE/DELETE) |
| Index Compression | 9i+ | 前綴重複度高的 B-tree 索引 | 全支援 |
| Hybrid Columnar Compression (HCC) | 11.2.0.3+ (Exadata/ZFS/Unity) | 極高壓縮比歸檔、數據倉庫 | 僅 Direct-path,需特定存儲 |
以 ACCOUNTS 表為例,區塊內多行重複值(如 STATUS='ACTIVE'、REGION='EAST')被提取至區塊頭部「符號表」,行內以 1-2 位元組符號取代原值。



關鍵特性:壓縮由後台觸發(非逐行即時),正常 DML 無 CPU 額外開銷;觸發壓縮時集中消耗 CPU,適合 OLTP 尖峰離峰特性。
-- 1. Baseline CTAS 無壓縮
CREATE TABLE t1 TABLESPACE users AS SELECT * FROM test WHERE rownum <= 50000;
-- 2. CTAS 啟用 BASIC
CREATE TABLE t2 COMPRESS BASIC TABLESPACE users AS SELECT * FROM test WHERE rownum <= 50000;
-- 3. 一般 INSERT 進壓縮表
CREATE TABLE t3 COMPRESS BASIC TABLESPACE users AS SELECT * FROM test WHERE rownum = 0;
INSERT INTO t3 SELECT * FROM test WHERE rownum <= 50000;
-- 4. Direct-path INSERT 進壓縮表
CREATE TABLE t4 COMPRESS BASIC TABLESPACE users AS SELECT * FROM test WHERE rownum = 0;
INSERT /*+ APPEND */ INTO t4 SELECT * FROM test WHERE rownum <= 50000;
-- 5. CTAS 無壓縮 → ALTER TABLE COMPRESS
CREATE TABLE t5 TABLESPACE users AS SELECT * FROM test WHERE rownum <= 50000;
ALTER TABLE t5 COMPRESS BASIC;
-- 6. 一般建表 → MOVE COMPRESS
CREATE TABLE t6 TABLESPACE users AS SELECT * FROM test WHERE rownum <= 50000;
ALTER TABLE t6 MOVE COMPRESS BASIC;
EXEC DBMS_STATS.GATHER_TABLE_STATS('SYS','T1'); ... T6;
SELECT table_name, blocks, pct_free, compression, compress_for
FROM user_tables WHERE table_name IN ('T1','T2','T3','T4','T5','T6');
ALTER TABLE MOVE COMPRESS 或等待自動壓縮觸發。| 維度 | Basic | OLTP |
|---|---|---|
| 空間節省 | 2x-4x | 1.5x-3x(略低於 Basic) |
| 查詢效能 | 掃描少區塊 → 提升 | 同 Basic,解壓開銷極小 |
| DML 效能 | 非 Direct-path 不壓縮,UPDATE 可能遷移行 | UPDATE 觸發重壓縮,CPU 微增 |
| 授權需求 | Enterprise Edition | Advanced Compression Option (額外授權) |