1.7 億筆記錄大表建立高效索引完整實錄：資源規劃、監控與統計資訊收集

建立過程深度觀察

• 階段 1（啟動前）：v$sort_segment.used_blocks=0, v$sort_usage 無資料。
• 階段 2（初始化）：捕捉到系統內部 INSERT INTO OBJ$... 建立索引字典物件。
• 階段 3（分區級排序循環）：used_blocks 從 0 遞增至 ~46,720，v$sort_usage 顯示活躍排序 Session，SQL 為 SELECT FILE# FROM FILE$ WHERE TS#=:1。隨後歸零。
• 階段 4（重複 14 次）：對應 14 個大分區依序建立索引，每個分區經歷完整「排序→寫入→釋放」週期。
• 字典可見性：建立期間 user_segments 查不到索引，實際以系統產生數字段名暫存；失敗自動回滾。

總耗時 99 分鐘 完成。

不可或缺的收尾：統計資訊收集

建立完成後若不收集統計資訊，優化器會誤判採用 INDEX FAST FULL SCAN 導致查詢極慢（測試 11 分鐘無結果）。

ANALYZE INDEX IDX_SUBMIT_RECORDTIME ESTIMATE STATISTICS;  -- 耗時 6.84 秒

收集後優化器轉為 INDEX RANGE SCAN，同樣查詢僅耗 5.37 秒，效能提升逾百倍。

最佳實踐總結

1. 雙重保障：PGA + TEMP 空間同時準備充足，避免多次磁碟排序往返。
2. 分區級監控：透過 v$sort_segment 觀察分區建立進度與資源消耗。
3. 統計資訊強制收集：索引建立與統計收集視為單一原子作業，缺一不可。
4. 生產化建議：改用 DBMS_STATS.GATHER_INDEX_STATS 並行收集；考慮 PARALLEL 子句加速索引建立（需評估 CPU/IO 壓力）。