Oracle 9i 快速全索引掃描 實戰：函數索引配合、參數調優與 Hint 強制技巧

快速全索引掃描 概念

Oracle 9i 強化 Fast Full Index Scan (FFS)：直接多區塊讀取索引區塊（類似全表掃描），不走 B-Tree 導航，不需訪問表。適用於：查詢欄位全包含於索引、需掃描大量行 (10%~25%)、不需排序。

啟用條件

• SELECT/WHERE 引用欄位全部存在於索引（含函數索引表達式）。
• 統計資訊完整，OPTIMIZER_MODE != RULE。
• 預估回傳行數達閾值（受 db_file_multiblock_read_count、optimizer_index_cost_adj、並行度影響）。
• 索引無 NULL 值（9i 前限制），9i+ 函數索引可包含 NULL。

函數索引 + FFS 實戰範例

-- 學生表
CREATE TABLE student (
  student_name VARCHAR2(40),
  date_of_birth DATE
);

-- 函數索引：大寫首字母 + 日期格式化
CREATE INDEX idx_student_fn ON student (
  INITCAP(student_name),
  TO_CHAR(date_of_birth, 'MM-DD-YY')
);

-- 觸發 FFS 的查詢 (完全匹配索引表達式)
SELECT * FROM student WHERE INITCAP(student_name) = 'Jones';
SELECT * FROM student WHERE TO_CHAR(date_of_birth, 'MM-DD-YY') = '04-07-85';

-- 強制 FFS + 並行
SELECT /*+ INDEX_FFS(s idx_student_fn) PARALLEL_INDEX(s idx_student_fn 4) */
       COUNT(*) FROM student s;

影響 FFS 成本的關鍵參數

Hint 強制與組合

-- 僅強制 FFS
SELECT /*+ INDEX_FFS(t idx_name) */ col FROM t WHERE ...;

-- FFS + 並行索引掃描
SELECT /*+ INDEX_FFS(t idx_name) PARALLEL_INDEX(t idx_name 8) */ col FROM t WHERE ...;

-- 指定並行查詢 (表級)
SELECT /*+ PARALLEL(t 8) INDEX_FFS(t idx_name) */ col FROM t WHERE ...;

實務建議

1. 優先收集統計：DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO')，讓 CBO 自主決策。
2. 手工計時驗證：對關鍵 SQL 執行 SET TIMING ON、AUTOTRACE，對比 FFS 與 INDEX RANGE SCAN 實際耗時。
3. 避免濫用 Hint：資料量、統計變化可能導致 Hint 反而劣化，建議用 SQL Plan Baseline 固定優良計畫。
4. 函數索引表達式必須與查詢完全一致：大小寫、格式化模板、NLS 參數皆需一致。

遷移到 9i 後的自動獲益

大量既有系統升級 9i 後，符合條件的查詢自動轉用 FFS，無需改碼。但需檢查 OPTIMIZER_INDEX_COST_ADJ 預設 100 是否適合新環境，建議設為 20~50 針對 OLTP。