億級資料分區表建立本地索引實戰：從參數調校到統計資訊收集

案例背景

目標表 STAT_SUBMIT_CENTER 為範圍分區表（依 MSGDATE 分 50 個分區），總大小 17.2 GB，記錄數 1.7 億筆。需在 RECORDTIME 欄位建立本地索引 IDX_SUBMIT_RECORDTIME。環境：SunOS 5.9, Oracle 9.2.0.4, 8 CPU, 3GB 記憶體。

前置評估與資源規劃

1. 磁碟空間：預估索引需數 GB 空間，確認資料表空間剩餘足夠。
2. PGA 記憶體：建立索引需大量排序，將 pga_aggregate_target 從 200MB 提升至 2GB（配合 workarea_size_policy=AUTO，單一 Session 可用 PGA = MIN(5% PGA, 100MB)）。
3. 暫存表空間：若記憶體不足會落盤排序，將 TEMP 表空間從 3.4GB 擴充至 8GB（參考社群經驗：15 億筆曾用 34GB）。
4. 離線建立：經評估決定停止業務存取，採用 NOLOGGING 非線上模式建立，縮短時間窗。

實際操作腳本化流程

-- 1. 擴充暫存檔
ALTER DATABASE TEMPFILE '/bgdata/Oracle/temp01.dbf' RESIZE 8192M;

-- 2. 調大 PGA
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=2048M;

-- 3. 背景執行建立索引腳本
nohup time createind.sh &

-- createind.sh 內容：
#!/bin/sh
sqlplus user/password <

ANALYZE INDEX IDX_SUBMIT_RECORDTIME ESTIMATE STATISTICS;