Oracle 索引重建策略：DROP/CREATE vs REBUILD vs REBUILD ONLINE 深度比較

索引碎片化會嚴重拖慢範圍掃描與全索引掃描效能。本文透過實測對比三種重建方式的鎖行為、空間需求、查詢可用性差異，幫助 DBA 選擇最適策略。

測試環境建置

CREATE TABLE test AS SELECT citycode c1 FROM citizeninfo2;  -- 1,600 萬筆
ALTER TABLE test MODIFY c1 NOT NULL;
CREATE INDEX idx_test_c1 ON test(c1);

鎖模式對比：CREATE vs REBUILD

查詢 V$LOCK (TM 型) 觀察基表鎖模式：

• CREATE INDEX：基表持有 **Mode 4 (Share Lock)**，阻擋所有 DML (Row-X, Mode 3 不相容)。
• ALTER INDEX REBUILD：同樣持有 **Mode 4 (Share Lock)**，同樣阻擋 DML。

結論：傳統 REBUILD **不具備** 線上 DML 能力，鎖行為與建立新索引相同。

REBUILD 關鍵優勢：舊索引保留機制

重建過程中，Oracle **不會立即刪除舊索引**，而是建立新段，驗證無誤後才切換字典指標並釋放舊段。

實測驗證：在另一 Session 執行 SELECT /*+ INDEX(test) */ COUNT(*) FROM test WHERE rownum < 10，執行計畫顯示仍使用 INDEX (FULL SCAN) OF 'IDX_TEST_C1'，證明查詢不中斷。

三大策略完整比較表

進階選項與注意事項

• REBUILD TABLESPACE new_ts：重建同時移動表空間，同樣需 2 倍空間。
• REBUILD COMPUTE STATISTICS：同步收集統計資訊，省去後續 DBMS_STATS 呼叫。
• PARALLEL n：啟用並行重建縮短時間，但會增加臨時段空間與 CPU 消耗。
• 不可重建場景：全域分區索引 (Global Partitioned Index) 僅支援 REBUILD PARTITION，不支援整體 REBUILD。

決策流程建議

1. 是否允許維護窗口？
   → 是 → 評估空間是否允許 2 倍？
       → 是 → REBUILD (查詢不中斷)
       → 否 → DROP/CREATE (節省空間)
   → 否 (7x24) → 是否 Enterprise Edition？
       → 是 → REBUILD ONLINE
       → 否 → 規劃停機窗口或使用 DBMS_REDEFINITION 線上重定義。