Oracle 索引原理與優化實戰指南

核心觀念：索引選擇決定效能生死

Oracle 提供多樣化的索引選項，正確選擇能讓執行數小時的查詢縮短為幾分鐘；錯誤選擇則可能導致死結、效能暴跌甚至進程終止。本文系統梳理各類索引的適用場景與避坑指南。

一、基礎索引概念與元數據查詢

掌握索引元數據視圖是優化的起點：

• DBA_INDEXES：列出資料庫所有索引（需 DBA 權限）
• USER_INDEXES：檢索當前 Schema 下的索引
• USER_IND_COLUMNS：查看指定表中被索引的具體欄位

二、複合索引（Composite Index）的演進與限制

複合索引包含多個欄位（如 empno、ename、deptno）。在 Oracle 9i 引入 Index Skip Scan 之前，查詢必須在 WHERE 條件中指定前導欄位才能利用該索引進行範圍掃描。9i 後可跳過前導欄位，但仍需評估選擇度。

關鍵提醒：Oracle 9i 前，僅當使用到索引的前導欄位時才能使用複合索引！

三、ROWID：直達資料的「導航圖」

每列擁有唯一 ROWID，索引透過它直接定位單列資料。可在任何表中直接引用 ROWID 欄位以檢查重複值或進行精確存取。

四、常見導致索引失效的陷阱與解法

4.1 不等於運算子（<>、!=）

-- 全表掃描
SELECT cust_id, cust_name FROM customers WHERE cust_rating <> 'aa';

-- 改寫後可使用索引（Rule-based Optimizer 環境）
SELECT cust_id, cust_name FROM customers 
WHERE cust_rating < 'aa' OR cust_rating > 'aa';

原則：將不等於轉換為 OR 條件，避免全表掃描。

4.2 IS NULL / IS NOT NULL

NULL 值未被索引定義（位圖索引除外），導致索引失效。建議：建表時將需索引的欄位設為 NOT NULL。

4.3 索引欄位上使用函數

-- 索引失效（無 Function-based Index 時）
SELECT * FROM emp WHERE TRUNC(hiredate) = '01-MAY-81';

-- 改寫利用索引範圍掃描
SELECT * FROM emp 
WHERE hiredate < TO_DATE('01-MAY-81') + 0.99999;

或建立 Function-based Index 直接支援函數運算。

4.4 隱性資料型別轉換

欄位為 VARCHAR2 卻與數值比較（如 WHERE account_number = 123），Oracle 會隱性轉換欄位導致索引失效。嚴禁隱性轉換， WHERE 條件型別必須與欄位定義一致。