Oracle 索引掃描四大類型深度解析：Unique/Range/Full/Fast Full Scan 選擇機制與調優

掃描類型決策樹

Optimizer 依「索引類型」+「WHERE 條件」+「統計資訊」決定掃描方式，理解四大類型邊界條件是讀懂執行計畫的關鍵。

1. Index Unique Scan (索引唯一掃描)

• 觸發條件：唯一索引 (PK/UK) + 等值條件 涵蓋所有引導欄位 + 預估回傳 ≤ 1 列。
• 範例：CREATE UNIQUE INDEX ux_emp ON emp(empno); SELECT * FROM emp WHERE empno = 7788;
• 特徵：單次 B-tree 遍歷直接定位 Leaf Block → ROWID → Table Access by Index Rowid (若需非索引欄位)。
• 組合索引注意：CREATE UNIQUE INDEX ux_emp ON emp(deptno, empno); 條件必須包含 deptno (引導欄)，否則退化為 Range Scan。

2. Index Range Scan (索引範圍掃描)

• 觸發條件 (任一)：
  1. 唯一索引 + 範圍運算子 (>, <, >=, <=, BETWEEN, LIKE 'prefix%')。
  2. 組合索引 + 僅部分引導欄位出現在條件 (導致可能回傳多列)。
  3. 非唯一索引 + 任意條件 (因可能回傳多列)。
• 範例：

  -- 唯一索引 + 範圍
  SELECT * FROM emp WHERE empno > 7500;
  
  -- 組合索引 (deptno, empno) 僅給 deptno
  SELECT * FROM emp WHERE deptno = 10;
  
  -- 非唯一索引
  CREATE INDEX ix_emp_dept ON emp(deptno);
  SELECT * FROM emp WHERE deptno = 10;

• 特徵：定位起始 Leaf Block → 順序掃描後續 Leaf Blocks → 收集 ROWID → Table Access。

3. Index Full Scan (索引全掃描)

• 觸發條件：僅 CBO 模式，且 Optimizer 認為「掃描整個索引結構」比「Full Table Scan」更廉價，通常發生於：
  1. 查詢欄位全包含於索引 (Covering Index)，無需回表。
  2. ORDER BY 欄位為索引引導欄，可避免排序。
  3. 統計資訊顯示索引極小 (如僅數個 Leaf Blocks)。
• 特徵：從 Root Block 走到最左 Leaf Block，然後雙向鏈表順序掃描所有 Leaf Blocks，**輸出有序** (依索引鍵值排序)。
• Hint：INDEX_FFS 強制 Fast Full Scan，INDEX 可能觸發 Full Scan。

4. Index Fast Full Scan (索引快速全掃描)

• 觸發條件：同 Index Full Scan，但 **不要求輸出有序**，且允許：
  1. 多區塊讀取 (DB_FILE_MULTIBLOCK_READ_COUNT)。
  2. 並行查詢 (PARALLEL Hint/表屬性)。
• 關鍵差異：

調優實戰：如何引導/避免特定掃描

總結

四大掃描類型形成「精準定位 → 範圍順讀 → 有序全讀 → 無序並行全讀」的效能遞增、適用面擴大階梯。掌握觸發條件與 Hint 控制，是 SQL 調優的核心技能。