萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> Oracle 教學

Oracle 函數索引與優化器強制使用索引實戰:UPPER/REPLACE/SUBSTR 場景破解

當 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() 改變了值,無法走索引

方案一:函數索引 FBI (Function-Based Index, 8i+)

-- 建立函數索引:直接索引 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';

FBI 關鍵限制

  • 查詢條件**必須完全匹配**索引表達式 (含函數名、參數順序、字面量格式)
  • UPPER(name) = 'JOHN' 能用,UPPER(name) = 'john' 也能用 (值已轉大寫),但 UPPER(name) LIKE 'JOHN%' 可能不走 (視版本/優化器)
  • 需收集表統計信息,**且需收集隱藏欄位統計**:DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR ALL HIDDEN COLUMNS SIZE AUTO')
  • DML 維護開銷:每次 INSERT/UPDATE 需計算函數值並維護索引結構

方案二:虛擬欄位 + 普通索引 (11g+ 推薦)

-- 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';         -- 優化器識別表達式等價,同樣走索引

虛擬欄位優於 FBI 的優勢

  • 可直接在查詢中引用虛擬欄位名,語義清晰
  • 支援統計信息獨立收集 (DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR COLUMNS NAME_UPPER'))
  • 可建立複合索引 (CREATE INDEX idx_name_upper_phone ON addr_book(name_upper, phone))
  • 避免 FBI 的表達式匹配脆弱性 (大小寫、空格、括號差異導致不匹配)

方案三:手動改寫條件強制索引範圍掃描 (無 DDL 權限/臨時方案)

-- 原查詢: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/虛擬欄位。

效能實測 (1000 萬行,查詢 UPPER(name)='JOHN')

方案執行時間邏輯讀索引大小DML 開銷
全表掃描 (無索引)12.4 秒1,250,000--
手動改寫 OR LIKE0.85 秒4,200--
函數索引 FBI0.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
copyright © 萬盛學電腦網 all rights reserved