Oracle 表壓縮技術全解析：Basic/OLTP/Hybrid Columnar 壓縮等級與適用場景

Oracle 表壓縮技術全解析：Basic/OLTP/Hybrid Columnar 壓縮等級與適用場景

隨著資料量增長，表壓縮成為節省儲存、減少記憶體佔用、提升查詢效能的關鍵手段。Oracle 11g R2 提供四種壓縮等級：Basic、OLTP、Warehouse (Hybrid Columnar)、Archive (Hybrid Columnar)。其中 Hybrid Columnar 僅支援 Exadata。本文詳細解析各壓縮方式的語法、觸發條件、CPU 開銷、分區表支援、壓縮狀態查詢、匯入匯出、還原轉換與限制事項。

壓縮方式對比總表

關鍵技術細節

1. 壓縮觸發條件差異

• Basic / HCC： 僅 直接路徑載入 觸發壓縮 (INSERT /*+ APPEND */, CTAS, SQL*Loader 直接路徑, 並行 INSERT)。傳統 INSERT/UPDATE 不壓縮。
• OLTP： 所有 SQL 操作 皆觸發壓縮 (傳統 INSERT/UPDATE + 直接路徑)。

2. Hybrid Columnar Compression (HCC) 原理

• 採用列式儲存取代傳統行式，相似資料集中儲存，大幅提升壓縮演算法效率
• 資料更新時：該行轉為行式儲存 (Rowid 改變)，未來更新較快，但壓縮等級降低
• 適用極少更新的資料 (資料倉儲、歷史歸檔)

3. 語法範例

-- Basic
CREATE TABLE sales_history (...) COMPRESS BASIC;
CREATE TABLE sales_history (...) COMPRESS;  -- 等同

-- OLTP
CREATE TABLE orders (...) COMPRESS FOR OLTP;

-- Warehouse (HCC)
CREATE TABLE sales_history (...) COMPRESS FOR QUERY [LOW|HIGH];  -- 預設 HIGH

-- Archive (HCC)
CREATE TABLE sales_history (...) COMPRESS FOR ARCHIVE [LOW|HIGH]; -- 預設 LOW

4. 直接路徑載入範例

INSERT /*+ APPEND */ INTO sales_history SELECT * FROM sales WHERE cust_id = 8890;
COMMIT;

分區表壓縮

• 單一分區表可混合壓縮/非壓縮分區，不同分區可用不同壓縮方式
• 分區級設定優先於表級設定
• 修改分區壓縮：
  • 僅新資料：ALTER TABLE ... MODIFY PARTITION ... COMPRESS ...
  • 新舊資料皆壓縮：ALTER TABLE ... MOVE PARTITION ... COMPRESS ... 或線上重定義 (DBMS_REDEFINITION)

壓縮狀態查詢

-- 表級
SELECT TABLE_NAME, COMPRESSION, COMPRESS_FOR FROM USER_TABLES;
-- 結果：ENABLED / DISABLED + BASIC / OLTP / QUERY HIGH / ARCHIVE LOW

-- 分區級
SELECT TABLE_NAME, PARTITION_NAME, COMPRESSION, COMPRESS_FOR FROM USER_TAB_PARTITIONS;

行級壓縮類型檢測 (HCC)

SELECT DECODE(DBMS_COMPRESSION.GET_COMPRESSION_TYPE(
    OWNNAME => 'HR', TABNAME => 'EMPLOYEES', ROW_ID => 'AAAVEIAAGAAAABTAAD'),
    1, 'No Compression',
    2, 'Basic or OLTP',
    4, 'HCC Query High',
    8, 'HCC Query Low',
    16, 'HCC Archive High',
    32, 'HCC Archive Low',
    'Unknown') FROM DUAL;

壓縮等級變更

• 非分區表：ALTER TABLE ... MOVE ... COMPRESS FOR ... (期間鎖表，不可 DML)
• 分區表：DBMS_REDEFINITION 線上重定義 (不鎖表，需額外空間)
• 表空間級：ALTER TABLESPACE ... SET DEFAULT COMPRESS ...

匯入匯出與還原

• IMPDP 預設保留壓縮屬性，非 HCC 表空間匯入 HCC 表會報錯
• 可用 TRANSFORM=SEGMENT_ATTRIBUTES:N 移除壓縮屬性匯入
• 非 HCC 表匯入時可透過表空間預設壓縮轉為 HCC
• HCC 表還原至非 Exadata：必須轉為 OLTP 或 NOCOMPRESS (ALTER TABLE MOVE COMPRESS FOR OLTP / NOCOMPRESS)

限制與注意事項

• 不支援線上 Segment Shrink
• 不適用 SecureFiles LOB (有獨立壓縮機制)
• 消耗 CPU，確保資源充足
• Basic 壓縮表：PCT_FREE=0；OLTP：PCT_FREE=10 (可覆蓋)
• Basic：不支援 加預設值欄位、刪除欄位
• OLTP：刪除欄位內部標記 UNUSED 避免解壓/重壓開銷
• 傳統 DML 導致非壓縮行時，需 ALTER TABLE MOVE 或線上重定義「打包」壓縮