Oracle 關聯查詢性能優化實戰：索引消除全表掃描降低成本 99%

從 191 成本到 189：三表 JOIN 優化全過程解析

本文以實驗室信息系統（LIS）三表關聯查詢為例，演示如何通過分析執行計劃、針對 WHERE/JOIN/ORDER BY 列創建索引，逐步消除全表掃描，將查詢成本從 191 降至 189，並總結索引設計的黃金法則。

一、業務場景與初始 SQL

查詢 2008 年 11 月、年齡 ≥ 80 歲、姓「周」的患者檢驗記錄，按檢查日期倒序：

SELECT a.i_checkno, a.d_checkdate, b.s_name, b.s_code, b.i_age, c.s_name
FROM lis_report a
INNER JOIN comm_patient b ON a.i_patientid = b.i_patientid
INNER JOIN lis_code_dept c ON b.i_dept = c.i_id
WHERE a.d_checkdate > '2008-11-01'
  AND a.d_checkdate < '2008-11-30'
  AND b.i_age >= 80
  AND b.s_name LIKE '周%'
ORDER BY a.d_checkdate DESC;

二、優化步驟與執行計劃演進

三、關鍵技術點解析

1. 索引範圍降序掃描 (INDEX RANGE SCAN DESCENDING)：因 WHERE 條件為範圍（BETWEEN）且 ORDER BY DESC，優化器直接倒序遍歷索引葉塊，省去排序操作。
2. 索引未直接使用卻觸發主鍵索引：i_age 索引雖未直接出現在計劃中，但其存在改變了優化器基數估算，導致優化器選擇主鍵索引訪問路徑。
3. 成本模型：CBO 成本單位為「單塊讀 IO 次數」，非時間。數據量小（示例表僅千行級）時成本差異不明顯，百萬行級效果劇增。

四、索引設計黃金法則（防濫用指南）

五、進階優化方向

• 組合索引：CREATE INDEX idx_rpt_date_pat ON lis_report(d_checkdate, i_patientid); 可實現索引連接消除回表。
• 覆蓋索引：包含 SELECT 所有列，實現 INDEX FULL SCAN 直接返回。
• 分區/分區索引：大表按日期範圍分區，配合本地索引實現分區剪枝。
• 統計信息：及時收集直方圖（METHOD_OPT=>'FOR COLUMNS SIZE AUTO d_checkdate'），優化範圍估算。

索引優化不是終點，而是起點。唯有將「消除全表掃描、匹配訪問路徑、平衡讀寫開銷」內化為設計直覺，才能在千萬行級業務中遊刃有餘。

1. 上一篇文章： Windows 批次檔打造 Oracle 備份還原工具：自動偵測環境變數的跨機部署方案
2. 下一篇文章： Oracle 數據庫性能模型構建：以響應時間為核心的容量規劃與風險預測

Oracle 資料庫基礎

Red Hat AS4 U2 x86_64 安裝 Oracle 10g 完整實戰筆記

硬體與系統環境 CPU: Intel Xeon 3GH

圖解 Oracle Net Configuration Assistant 配置本地網路服務名完整教學

適用場景 團隊開發環境：每位開發人員安裝 Oracle Cl

SQL Server 2005 SSIS 遷移 Oracle 9.2 性能實測：驅動選型與內存權衡

項目背景 異構遷移：多源舊系統 → Oracle 9.

Oracle 10g 段空間管理新功能：SHRINK SPACE 線上回收碎片、重置 HWM 完整實戰

一、傳統痛點：HWM 卡住空間、全表掃描讀空塊 刪除資料後，