-- 每日批次腳本 (TRUNCATE 後、載入前/後執行)
TRUNCATE TABLE t_big; -- 1. 清空表、重置 HWM
-- 可選：DROP INDEX ... / 但 11g+ 索引可保留，僅標記 UNUSABLE 再 REBUILD 更快
-- DROP INDEX idx_t_big_1; DROP INDEX idx_t_big_2; ...
-- 2. 載入當日數據 (INSERT /*+ APPEND */ 或 SQL*Loader/Data Pump)
INSERT /*+ APPEND */ INTO t_big SELECT ... FROM stage_table;
COMMIT;
-- 3. 重建索引 (若已 DROP) 或 REBUILD (若標記 UNUSABLE)
ALTER INDEX idx_t_big_1 REBUILD;
ALTER INDEX idx_t_big_2 REBUILD;
-- 並行重建大幅縮短時間
-- ALTER INDEX idx_t_big_1 REBUILD PARALLEL 4;
-- 4. **關鍵：收集統計資訊** (CASCADE=>TRUE 同步收集索引統計)
EXEC DBMS_STATS.GATHER_TABLE_STATS(
ownname => 'SCHEMA',
tabname => 'T_BIG',
cascade => TRUE, -- 同步索引統計
method_opt => 'FOR ALL COLUMNS SIZE AUTO', -- 直方圖自動
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, -- 11g+ 自動採樣
degree => 4 -- 並行收集
);
-- 5. 驗證執行計畫
EXPLAIN PLAN FOR UPDATE ...; -- 確認走索引、成本合理BEGIN
DBMS_SCHEDULER.CREATE_JOB(
job_name => 'JOB_GATHER_T_BIG_STATS',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN DBMS_STATS.GATHER_TABLE_STATS(''SCHEMA'',''T_BIG'',CASCADE=>TRUE,METHOD_OPT=>''FOR ALL COLUMNS SIZE AUTO'',DEGREE=>4); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY; BYHOUR=3; BYMINUTE=0', -- 凌晨 3 點
enabled => TRUE,
comments => 'T_BIG 每日載入後統計收集，防止計畫劣化'
);
END;
/
-- 並開啟自動統計收集 (11g+ 預設開啟)
EXEC DBMS_STATS.SET_GLOBAL_PREFS('AUTOSTATS_TARGET','AUTO');| 策略 | 操作 | 優點 | 缺點 | 建議場景 |
|---|---|---|---|---|
| 保留索引 + REBUILD | `ALTER INDEX ... REBUILD` | 不需 DROP/CREATE 權限、保留授權/同義詞 | 索引段空間不釋放 (若表大幅縮小) | 表大小波動不大、權限受限 |
| DROP + CREATE | `DROP INDEX ...; CREATE INDEX ...` | 徹底釋放空間、可調整索引定義 | 需 DDL 權限、同義詞/授權需重建 | 表大幅縮小、結構調整 |
| 標記 UNUSABLE + REBUILD | `ALTER INDEX ... UNUSABLE; ... REBUILD` | 載入數據時跳過索引維護 (快)、載入後並行重建 | 載入期間查詢不可用該索引 | **大量載入首選** (配合 `SKIP_UNUSABLE_INDEXES=TRUE`) |
**TRUNCATE 重置數據但不重置統計**，導致 CBO 「以訛傳訛」產生劣質計畫。**「載入 → 重建索引 → 收集統計 (CASCADE=TRUE)」三部曲是大表日刷新的鐵律**。配合 `DBMS_SCHEDULER` 固化為自動化作業，並啟用 `AUTO_SAMPLE_SIZE` + 並行收集，將「統計資訊失效導致 SQL 慢」風險納入常態化管控。
