萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 表與索引分離存儲最佳實務:不同表空間配置與移動腳本

前言

Oracle 表空間本身不區分「表表空間」與「索引表空間」,小資料量下同一表空間無妨。但隨著資料量增長,將表與索引分置於不同表空間(甚至不同物理磁碟/ASM Disk Group)成為效能與可管理性的最佳實務。本文闡述分離的兩大核心效益、索引移動語法、批量生成移動腳本、以及新建表時指定索引表空間的完整範例。

一、為什麼要分離表與索引表空間?

1.1 效能提升:I/O 隔離

  • 表存取多為循序/散發讀取(全表掃描、範圍掃描)。
  • 索引存取多為隨機單區塊讀取(索引範圍掃描、唯一掃描)。
  • 將兩類 I/O 模式分離至不同物理磁碟/控制器/ASM Disk Group,可並行化磁碟臂移動,減少爭用,提升整體吞吐量。
  • 現代 SSD/全快閃陣列雖降低隨機讀取懲罰,但頻寬隔離、QoS 控制、快照/備份獨立管理仍具價值。

1.2 管理便利性:故障隔離與恢復彈性

  • 索引損壞(媒體錯誤、邏輯損壞)時,僅需 REBUILD 索引,資料不遺失,恢復時間極短(分鐘級)。
  • 表資料檔損壞需從備份恢復,涉及 Redo Apply,耗時較長(小時級)。
  • 分離後可針對索引表空間採用更激進的備份策略(如僅備份元數據、更頻繁快照),因索引可重建。
  • 表空間層級的 READ ONLY、OFFLINE、TRANSPORTABLE 操作更靈活。

二、移動現有索引至新表空間

2.1 單一索引移動

ALTER INDEX index_owner.index_name REBUILD TABLESPACE new_index_tbs;
  • REBUILD 會重建索引結構,過程中獲得 DML 鎖(ONLINE 選項可減少鎖定,Enterprise Edition 支援)。
  • 移動完成後,索引段落在新表空間,原表空間空間釋放需 ALTER TABLESPACE ... COALESCE 或等待 SMON 整理。

2.2 批量生成 Schema 級所有索引移動腳本

SET PAGESIZE 0 LINESIZE 200 FEEDBACK OFF ECHO OFF
SPOOL move_indexes.sql

SELECT 'ALTER INDEX ' || owner || '.' || index_name || 
       ' REBUILD TABLESPACE index_tbs PARALLEL 4 ONLINE;'
FROM dba_indexes
WHERE table_owner = 'TARGET_SCHEMA'  -- 指定表擁有者
  AND tablespace_name != 'INDEX_TBS' -- 排除已在目標表空間
ORDER BY index_name;

SPOOL OFF
-- 執行生成的腳本
@move_indexes.sql

關鍵選項說明:

  • PARALLEL 4:並行重建加速(需 Enterprise Edition)。
  • ONLINE:允許重建期間 DML 操作(11g+ Enterprise Edition)。
  • 若為 Standard Edition 或舊版本,移除 PARALLEL/ONLINE,安排維護窗口執行。

三、新建表時指定索引表空間(關鍵細節)

分離策略落地的關鍵在於建表時即指定索引表空間,否則 PK/UK 索引會跟著表建在同一表空間。

3.1 完整建表示例

CREATE TABLE tmp (
    mon       VARCHAR2(6),
    idno      VARCHAR2(10),
    -- 主鍵索引指定表空間
    CONSTRAINT pk_name PRIMARY KEY (mon, idno)
        USING INDEX TABLESPACE index_tbs,
    -- 唯一約束索引指定表空間
    CONSTRAINT uk_name UNIQUE (mon)
        USING INDEX TABLESPACE index_tbs
) TABLESPACE data_tbs;  -- 表資料放在 data_tbs

3.2 關鍵語法點

  • USING INDEX TABLESPACE index_tbs 子句**必須緊接在約束定義後**,適用於 PRIMARY KEY、UNIQUE。
  • 外鍵 (FOREIGN KEY) **不建立索引**(Oracle 不自動建立 FK 索引),需手動建立並指定表空間:CREATE INDEX idx_fk ON child(col) TABLESPACE index_tbs;
  • TABLESPACE data_tbs 在表層級指定表資料儲存位置。

四、驗證與維運檢查

4.1 確認表與索引表空間分離

SELECT t.table_name, t.tablespace_name AS table_ts, 
       i.index_name, i.tablespace_name AS index_ts
FROM user_tables t
JOIN user_indexes i ON t.table_name = i.table_name
WHERE t.tablespace_name != i.tablespace_name
ORDER BY t.table_name;

4.2 定期檢查索引碎片與重建需求

SELECT index_name, blevel, leaf_blocks, distinct_keys, 
       avg_leaf_blocks_per_key, avg_data_blocks_per_key,
       clustering_factor
FROM user_indexes
WHERE tablespace_name = 'INDEX_TBS'
ORDER BY leaf_blocks DESC;

五、常見誤區與避坑

誤區正確認知
「索引一定要跟表分開」小表、唯讀表、單磁碟環境無需強制分離,增加管理複雜度
「REBUILD 會自動釋放舊表空間空間」REBUILD 在新表空間建立新段,舊段標記為可重用,需 COALESCE 或 SHRINK 真正釋放
「PK/UK 索引會自動跟著表走」預設確實如此,**必須顯式 USING INDEX TABLESPACE** 指定
「分離後備份只要備表表空間」索引雖可重建,但重建需時間與資源,建議仍納入備份,頻率可降低

六、結語

表與索引分離存儲是 Oracle 大型資料庫架構的經典最佳實務。核心在於:I/O 模式隔離提升效能、故障域隔離縮短恢復時間、管理操作粒度更細。落地關鍵:建表時 USING INDEX TABLESPACE、既有索引批量 REBUILD、納入標準化部署模板。現代全快閃儲存雖弱化 I/O 隔離效益,但管理彈性與故障隔離價值長存。

copyright © 萬盛學電腦網 all rights reserved