Oracle CBO 對 LIKE 謂詞選擇率估算機制實測

Oracle CBO 對 LIKE 謂詞選擇率估算機制實測

優化器 (CBO) 對 LIKE 條件的基數估算直接影響執行計劃選擇。本文通過實測驗證不同 LIKE 模式的選擇率計算邏輯。

實驗環境準備

DROP TABLE test PURGE;
CREATE TABLE test AS SELECT * FROM dba_objects;
EXEC dbms_stats.gather_table_stats(USER, 'TEST');
SELECT COUNT(*) FROM test;  -- 79,747 行

實測數據分佈

SELECT COUNT(*) FROM test WHERE object_name LIKE 'test%';   -- 0 行
SELECT COUNT(*) FROM test WHERE object_name LIKE '%test%';  -- 12 行
SELECT COUNT(*) FROM test WHERE object_name LIKE '%test';   -- 2 行

執行計劃觀察 (SET AUTOTRACE TRACE EXP)

情況 1：前導通配符 %test% / %test

SELECT * FROM test WHERE object_name LIKE '%test%';
-- Plan hash: 1357081020
-- Rows (估算): 3,987  = 79,747 × 5%
-- Operation: TABLE ACCESS FULL

SELECT * FROM test WHERE object_name LIKE '%test';
-- Rows (估算): 3,987  = 79,747 × 5%

情況 2：後置通配符 test%

SELECT * FROM test WHERE object_name LIKE 'test%';
-- Rows (估算): 2  (根據直方圖/密度精確估算)

情況 3：單字符通配符 t%

SELECT * FROM test WHERE object_name LIKE 't%';
-- Rows (估算): 1,079 (根據首字母頻率估算)

核心結論

原理深度解析

• CBO 內部對「前導通配符」視為**未知值篩選**，回退到默認選擇率常數 _optimizer_like_selectivity = 0.05 (隱藏參數，可修改但不建議)。
• 後置通配符可利用列統計信息 (DBA_TAB_COL_STATISTICS 中的 DENSITY、NUM_DISTINCT、HISTOGRAM) 進行精確估算。
• 11g+ DBMS_STATS.GATHER_TABLE_STATS 默認 METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO' 會自動收集高頻列直方圖，顯著提升 LIKE 估算準確度。

優化建議

1. 避免前導 %：業務允許時改為後置 % 或全文檢索 (Oracle Text / JSON 搜索)。
2. 收集直方圖：EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TAB', METHOD_OPT => 'FOR COLUMNS SIZE 254 COL_NAME')
3. 函數索引：CREATE INDEX idx_upper_name ON test(UPPER(object_name)) 配合 WHERE UPPER(object_name) LIKE 'TEST%'。
4. 動態採樣：OPT_DYNAMIC_SAMPLING 或 Hint /*+ DYNAMIC_SAMPLING(4) */ 讓優化器運行時採樣修正估算。
5. Cardinality Hint：/*+ CARDINALITY(t 100) */ 強制指定估算行數 (臨時方案)。

驗證腳本

SELECT table_name, column_name, num_distinct, density, histogram
FROM user_tab_col_statistics
WHERE table_name = 'TEST' AND column_name = 'OBJECT_NAME';