Oracle 執行計劃「無私」健壯性：B-Tree 索引與 NOT NULL 約束的優化智慧

Oracle 執行計劃「無私」健壯性：B-Tree 索引與 NOT NULL 約束的優化智慧

Oracle 優化器會在開發者給出錯誤 Hint 時仍選擇正確執行計劃，這種「無私」特性體現了資料庫核心邏輯的健壯性。本文透過一個簡單實驗，展示 B-Tree 索引不存儲 NULL 值的特性，如何導致全表掃描而非索引掃描，以及加上 NOT NULL 約束後優化器如何果斷轉向索引快速全掃描，甚至允許 Hint 生效。

實驗環境

• 測試表 TBL_PLAN，欄位 ID NUMBER
• 收集統計資訊
• 建立 B-Tree 索引 IDX_TBL_PLAN_ID ON TBL_PLAN(ID)

階段 1：無 NOT NULL 約束

SELECT ID FROM TBL_PLAN;

執行計劃： TABLE ACCESS FULL TBL_PLAN (全表掃描)

即使加上 Hint：

SELECT /*+ INDEX(TBL_PLAN IDX_TBL_PLAN_ID) */ ID FROM TBL_PLAN;

結果： 仍為全表掃描，Hint 被忽略。

核心原因

B-Tree 索引不存儲完全為 NULL 的鍵值。 雖然表中實際無 NULL，但優化器僅根據中繼資料 (欄位定義允許 NULL) 判斷：若從索引讀取，可能遺漏 NULL 行，導致結果錯誤。為保證正確性，優化器拒絕使用索引，寧選全表掃描。

階段 2：加上 NOT NULL 約束

ALTER TABLE TBL_PLAN MODIFY (ID NOT NULL);

相當於將索引轉為「不含 NULL」的語義保證。

再次查詢

SELECT ID FROM TBL_PLAN;

執行計劃： INDEX FAST FULL SCAN IDX_TBL_PLAN_ID (索引快速全掃描) —— 最佳路徑！

加上 Hint 測試

SELECT /*+ INDEX(TBL_PLAN IDX_TBL_PLAN_ID) */ ID FROM TBL_PLAN;

結果： 此時 Hint 生效，執行計劃顯示 INDEX RANGE SCAN (或 INDEX FULL SCAN)，優化器尊重開發者意圖。

總結與啟示

啟示：

1. 正確的約束定義是優化器做最佳決策的前提：NOT NULL、PRIMARY KEY、UNIQUE、CHECK 等約束不僅是業務規則，更是統計資訊的重要輸入。
2. 優化器「無私」於保護正確性：即使開發者給錯 Hint，優化器也會拒絕可能產生錯誤結果的路徑。
3. 應用系統同樣應具備這種健壯性：在不確定或衝突時，選擇安全、正確的預設行為，而非盲目執行指令。

1. 上一篇文章： Oracle 實用單行函數速查手冊：字串/數字/日期/轉換/通用/條件
2. 下一篇文章： Oracle 表壓縮技術全解析：Basic/OLTP/Hybrid Columnar 壓縮等級與適用場景

Oracle 教學

Oracle 實用統計 SQL 三大場景：行轉列、分組拼接、分組取首行

Oracle 實用統計 SQL 三大場景：行轉列、分組拼接、

Oracle Database Link創建的兩種方式圖文詳解（GUI與SQL命令）

場景描述 從A數據庫連接B數據庫：IP192.168.1.7

Oracle 資料表死鎖情況查找與解除完整操作指南

Oracle 資料表死鎖情況查找與解除完整操作指南 從鎖定物

Oracle 常用資料型別完整分類與選擇指南

三大類別概覽 1. 字元型別 型別長度範圍特性適用場景