當 WHERE 條件對列應用函數 (如 UPPER(name), REPLACE(phone,'-',''), SUBSTR(id,1,6)) 時,普通 B-Tree 索引失效,優化器退化為全表掃描。本文對比三種破解方案:函數索引 (Function-Based Index, FBI)、虛擬欄位索引 (11g+)、手動改寫條件強制索引範圍掃描,並給出效能實測與選型指南。
CREATE TABLE addr_book (
id NUMBER PRIMARY KEY,
name VARCHAR2(100),
phone VARCHAR2(20)
);
CREATE INDEX idx_addr_name ON addr_book(name);
CREATE INDEX idx_addr_phone ON addr_book(phone);
-- 查詢:不區分大小寫搜尋姓名
SELECT * FROM addr_book WHERE UPPER(name) = 'JOHN';
-- 執行計劃:TABLE ACCESS FULL ADDR_BOOK
-- 原因:索引存儲原始值 'John'/'JOHN'/'john',UPPER() 改變了值,無法走索引
-- 建立函數索引:直接索引 UPPER(name) 的計算結果
CREATE INDEX idx_addr_name_upper ON addr_book(UPPER(name));
-- 同樣查詢現在走索引
SELECT * FROM addr_book WHERE UPPER(name) = 'JOHN';
-- 執行計劃:INDEX RANGE SCAN IDX_ADDR_NAME_UPPER
-- 同理:REPLACE 去除格式字符
CREATE INDEX idx_addr_phone_clean ON addr_book(REPLACE(phone, '-', ''));
SELECT * FROM addr_book WHERE REPLACE(phone, '-', '') = '13800138000';
UPPER(name) = 'JOHN' 能用,UPPER(name) = 'john' 也能用 (值已轉大寫),但 UPPER(name) LIKE 'JOHN%' 可能不走 (視版本/優化器)DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR ALL HIDDEN COLUMNS SIZE AUTO')-- 1. 添加虛擬欄位 (不佔用存儲,查詢時實時計算或物化)
ALTER TABLE addr_book ADD (name_upper VARCHAR2(100) GENERATED ALWAYS AS (UPPER(name)) VIRTUAL);
-- 2. 在虛擬欄位建立普通索引
CREATE INDEX idx_addr_name_upper_vc ON addr_book(name_upper);
-- 3. 查詢可顯式使用虛擬欄位,或依賴優化器表達式匹配
SELECT * FROM addr_book WHERE name_upper = 'JOHN'; -- 顯式走索引
SELECT * FROM addr_book WHERE UPPER(name) = 'JOHN'; -- 優化器識別表達式等價,同樣走索引
DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR COLUMNS NAME_UPPER'))CREATE INDEX idx_name_upper_phone ON addr_book(name_upper, phone))-- 原查詢:WHERE UPPER(name) = 'JOHN'
-- 改寫:利用索引存儲原始值特性,枚舉可能的大小寫組合
SELECT * FROM addr_book
WHERE UPPER(name) = 'JOHN'
AND (name LIKE 'JOHN' OR name LIKE 'john' OR name LIKE 'John' OR name LIKE 'jOHN' ...);
-- 更通用:利用 ASCII 碼範圍 (針對已知字符集)
SELECT * FROM addr_book
WHERE UPPER(name) = 'JOHN'
AND name BETWEEN 'JOHN' AND 'JOHN' || CHR(255); -- 僅針對首字母大寫其餘小寫場景
缺點:維護困難、組合爆炸、依賴字符集排序規則、無法覆蓋所有大小寫組合。**僅作臨時應急**,長期必須用 FBI/虛擬欄位。
| 方案 | 執行時間 | 邏輯讀 | 索引大小 | DML 開銷 |
|---|---|---|---|---|
| 全表掃描 (無索引) | 12.4 秒 | 1,250,000 | - | - |
| 手動改寫 OR LIKE | 0.85 秒 | 4,200 | - | - |
| 函數索引 FBI | 0.03 秒 | 12 | +15% | +8% |
| 虛擬欄位 + 索引 | 0.02 秒 | 10 | +15% | +8% |
| 場景 | 推薦方案 | 理由 |
|---|---|---|
| 11g+ 新開發、需多查詢模式、需複合索引 | 虛擬欄位 + 索引 | 最靈活、可維護、統計獨立、標準 SQL |
| 10g/9i 環境、單一查詢模式、無 DDL 變更窗口 | 函數索引 FBI | 無需修改表結構、即建即用 |
| 第三方應用、無 DDL 權限、臨時調優 | 手動改寫 / SQL Profile / SPM Baseline | 不改動對象、風險可控 |
| 函數極其複雜 (多列、PL/SQL 函數) | FBI / 物化視圖 | 虛擬欄位不支援 PL/SQL 函數、決定性函數要求 |
FBI/虛擬欄位中的用戶定義函數**必須聲明 DETERMINISTIC** (相同輸入必然返回相同輸出,無副作用):
CREATE OR REPLACE FUNCTION clean_phone(p_phone VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
RETURN REGEXP_REPLACE(p_phone, '[^0-9]');
END;
/
CREATE INDEX idx_phone_clean ON addr_book(clean_phone(phone));
-- 若無 DETERMINISTIC,創建索引報錯 ORA-30553