Oracle SQL 調優核心四大準則與索引設計優化策略

SQL 調優終極目標：四大核心準則

1. 消除不必要的大表全表掃描
   有序表回傳 <40% 行、無序表回傳 <7% 行時，應使用索引。手段：新增 B-tree/位圖/函數索引、使用 INDEX Hint 強制走索引、對比 I/O 成本決定。
2. 小表全表掃描快取化
   當全表掃描確為最佳路徑時，將小表置入 KEEP buffer pool，減少物理讀。
3. 確保最優索引使用
   多索引可選時，驗證優化器選擇是否最優，必要時用 Hint 指定。
4. 確保最優 JOIN 方式
   Nested Loop / Hash Join / Sort-Merge Join 各有適用場景，依資料量與連接鍵分佈選擇。

這四條準則涵蓋 100% SQL 調優任務。

索引設計與優化深度解析

索引類型選擇指南

索引設計黃金法則

• 大表建索引效益明顯；小表同樣受益於避免全表掃描
• 主鍵/唯一鍵自動建立索引，提供唯一性驗證
• LONG/LONG RAW 不可建索引，其餘類型皆可
• 定期監控 V$OBJECT_USAGE 識別未用索引並移除
• 重建索引時機：高度 >4、葉子塊碎片率高、大量 DML 後

實戰優化檢查清單

1. 收集統計資訊：DBMS_STATS.GATHER_TABLE_STATS（CBO 前提）
2. 檢視執行計劃：EXPLAIN PLAN + DBMS_XPLAN.DISPLAY
3. 識別全表掃描大表 → 評估索引覆蓋率
4. 檢查 JOIN 順序與方式 → 必要時用 LEADING/USE_HASH 等 Hint
5. 驗證索引使用率 → 移除無效索引、補齊缺失索引