Oracle 表空間本身不區分「表表空間」與「索引表空間」,小資料量下同一表空間無妨。但隨著資料量增長,將表與索引分置於不同表空間(甚至不同物理磁碟/ASM Disk Group)成為效能與可管理性的最佳實務。本文闡述分離的兩大核心效益、索引移動語法、批量生成移動腳本、以及新建表時指定索引表空間的完整範例。
READ ONLY、OFFLINE、TRANSPORTABLE 操作更靈活。ALTER INDEX index_owner.index_name REBUILD TABLESPACE new_index_tbs;
REBUILD 會重建索引結構,過程中獲得 DML 鎖(ONLINE 選項可減少鎖定,Enterprise Edition 支援)。ALTER TABLESPACE ... COALESCE 或等待 SMON 整理。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)。PARALLEL/ONLINE,安排維護窗口執行。分離策略落地的關鍵在於建表時即指定索引表空間,否則 PK/UK 索引會跟著表建在同一表空間。
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
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 在表層級指定表資料儲存位置。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;
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 隔離效益,但管理彈性與故障隔離價值長存。