實戰案例：分頁查詢 20 筆耗時 16 秒，執行計畫顯示全表掃描。優化後改用索引範圍掃描，耗時降至毫秒級。根因在 WHERE 條件對索引欄使用函數導致索引失效。
| # | 陷阱 | 失效範例 | 改寫方案 |
|---|---|---|---|
| 1 | WHERE 條件對索引欄使用函數 | WHERE TRUNC(birthdate) = '01-MAY-82' | 建立函數索引 CREATE INDEX ... ON t(TRUNC(birthdate))；或改寫 WHERE birthdate >= DATE '1982-05-01' AND birthdate < DATE '1982-05-02' |
| 2 | 隱式資料類型轉換 | WHERE dept_id = 900198 (dept_id 為 VARCHAR2) | 常數加引號 WHERE dept_id = '900198'；應用端使用繫結變數 |
| 3 | 不等於操作 (<>, !=, NOT >=, NOT <=) | WHERE status <> 0 | 改用 WHERE status > 0 OR status < 0 (配合索引)；或接受全掃 |
| 4 | LIKE '%abc' 開頭萬用字元 | WHERE name LIKE '%son' | 若固定後綴，建立反轉函數索引 REVERSE(name)；否則全掃 |
| 5 | IS NULL / IS NOT NULL | WHERE comm IS NULL | 單欄索引不存全 NULL；組合索引非全 NULL 會被索引；考慮 NVL(comm,-1) 函數索引 |
| 6 | 複合索引不合併 (等值+範圍) | WHERE job='MGR' AND deptno>10 (兩非唯一索引) | 建立組合索引 (job, deptno)；或接受單一索引存取 |
| 7 | 無 WHERE 條件 | SELECT * FROM big_table | 加上過濾條件；或使用分頁/抽樣 |
WHERE TO_NUMBER(TO_CHAR(TO_DATE(tran_date,'yyyy-mm-dd hh24:mi:ss'),'YYYYMMDD')) >= ... -- 執行計畫: TABLE ACCESS FULL, Cost 188776
WHERE tran_date >= ? AND tran_date <= ? AND clear_date >= ? AND clear_date <= ? -- 執行計畫: INDEX RANGE SCAN DESCENDING, Cost 8
Oracle 對非唯一索引不支援 Index Merge (Bitmap AND/OR 僅限位圖索引)。建立組合索引 (tran_date, clear_date) 可同時服務兩欄條件。
SELECT index_name, index_type, uniqueness, status FROM user_indexes WHERE table_name = 'TRADEWASTE'; EXPLAIN PLAN FOR SELECT ...; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
EXPLAIN PLAN 驗證所有核心 SQL 走索引。DBMS_STATS.GATHER_TABLE_STATS) 確保優化器決策準確。
