Oracle 表與索引分離存儲：不同表空間配置、遷移腳本與主鍵約束指定索引表空間

隨著數據量增長，表與索引共置同一表空間導致 IO 爭用。本文說明分離收益、遷移命令、批量生成腳本、新建表時約束指定索引表空間完整方案。

為什麼要分離？

單索引遷移命令

ALTER INDEX index_owner.index_name REBUILD TABLESPACE new_index_tbs;

批量生成遷移腳本 (Schema 級)

SELECT 'ALTER INDEX ' || owner || '.' || index_name || ' REBUILD TABLESPACE index_tbs;'
FROM dba_indexes
WHERE table_owner = 'SCHEMA_NAME'
ORDER BY index_name;

輸出結果直接複製執行，或 SPOOL 到文件 @rebuild_indexes.sql。

新建表時：主鍵/唯一約束指定索引表空間

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
);

關鍵語法：USING INDEX TABLESPACE <tablespace_name> 子句，適用於 PK/UK 約束創建時隱式建立的索引。

驗證分離效果

SELECT table_name, tablespace_name AS table_tbs
FROM dba_tables WHERE owner = 'SCHEMA_NAME';

SELECT index_name, tablespace_name AS index_tbs
FROM dba_indexes WHERE table_owner = 'SCHEMA_NAME';

注意事項

• 分離前評估：小數據量 (<100MB) 收益不明顯，維護成本可能大於收益
• 索引表空間建議：EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M 減少碎片
• 分區表：GLOBAL INDEX 同理可指定表空間；LOCAL INDEX 隨分區走
• RAC 環境：確保目標表空間在所有節點掛載路徑一致