以下各節示範幾種常見的「混淆條件」手法。所謂被混淆的條件（obfuscated condition），是指以某種寫法表達的 where 子句，阻止了索引被正確使用。本節是一份反模式清單，每位開發者都該認得並避開。

日期型別#

多數混淆都與 DATE 型別有關。Oracle 在這方面特別脆弱，因為它只有一種 DATE 型別，而且永遠帶有時間部分。

TRUNC 的陷阱#

用 TRUNC 函式移除時間部分已成慣例。實際上它並沒有移除時間，而是把時間設為午夜（因為 Oracle 沒有純粹的日期型別）。要在搜尋時忽略時間，可以在比較的兩邊都用 TRUNC——例如查昨天的銷售：

SELECT ...
  FROM sales
 WHERE TRUNC(sale_date) = TRUNC(sysdate - INTERVAL '1' DAY)

這是完全有效且正確的敘述，卻無法正確使用 SALE_DATE 上的索引。原理與「用 UPPER / LOWER 做大小寫不敏感搜尋」相同：TRUNC(sale_date) 與 SALE_DATE 是完全不同的東西，函式對資料庫而言是黑盒子。

解法之一是函式索引：

CREATE INDEX index_name
          ON table_name (TRUNC(sale_date))

但這樣一來，你就必須永遠在 where 子句中使用 TRUNC(date_column)。若使用不一致——時而加、時而不加——你就需要兩個索引！

沒有函式索引的資料庫怎麼辦#

即使資料庫有純粹的日期型別，查詢較長區間時同樣會出問題（MySQL 範例）：

SELECT ...
  FROM sales
 WHERE DATE_FORMAT(sale_date, "%Y-%M")
     = DATE_FORMAT(now()    , "%Y-%M")

這也是完全正確的查詢、同樣的問題，但上面的解法在此不適用——MySQL 沒有函式索引。

替代方案是使用明確的範圍條件，這是適用於所有資料庫的通解：

SELECT ...
  FROM sales
 WHERE sale_date BETWEEN quarter_begin(?)
                     AND quarter_end(?)

如果你做過前面「找出所有 42 歲員工」的練習，應該會認出這個模式。

只要 SALE_DATE 上有一個普通索引就足以優化這段查詢。QUARTER_BEGIN 與 QUARTER_END 負責計算邊界日期。計算可能稍微複雜，因為 between 一律包含邊界值——當 SALE_DATE 帶有時間部分時，QUARTER_END 必須回傳「下一季第一天之前的那一瞬間」。這些邏輯都可以藏在函式裡。

    sale_date >= TRUNC(sysdate)
AND sale_date <  TRUNC(sysdate + INTERVAL '1' DAY)

CREATE FUNCTION quarter_begin(dt DATETIME)
RETURNS DATETIME DETERMINISTIC
RETURN CONVERT
       (
          CONCAT
          ( CONVERT(YEAR(dt),CHAR(4))
          , '-'
          , CONVERT(QUARTER(dt)*3-2,CHAR(2))
          , '-01'
          )
       , datetime
       );

CREATE FUNCTION quarter_end(dt DATETIME)
RETURNS DATETIME DETERMINISTIC
RETURN DATE_ADD
       ( DATE_ADD ( quarter_begin(dt), INTERVAL 3 MONTH )
       , INTERVAL -1 MICROSECOND);

CREATE FUNCTION quarter_begin(dt IN DATE)
RETURN DATE
AS
BEGIN
   RETURN TRUNC(dt, 'Q');
END;
/

CREATE FUNCTION quarter_end(dt IN DATE)
RETURN DATE
AS
BEGIN
   -- Oracle DATE 型別解析度為秒
   -- 從下一季第一天減去一秒
   RETURN TRUNC(ADD_MONTHS(dt, +3), 'Q')
        - (1/(24*60*60));
END;
/

CREATE FUNCTION quarter_begin(dt timestamp with time zone)
RETURNS timestamp with time zone AS $$
BEGIN
     RETURN date_trunc('quarter', dt);
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION quarter_end(dt timestamp with time zone)
RETURNS timestamp with time zone AS $$
BEGIN
   RETURN date_trunc('quarter', dt)
          + interval '3 month'
          - interval '1 microsecond';
END;
$$ LANGUAGE plpgsql;

CREATE FUNCTION quarter_begin (@dt DATETIME)
RETURNS DATETIME
BEGIN
   RETURN DATEADD (qq, DATEDIFF (qq, 0, @dt), 0)
END
GO

CREATE FUNCTION quarter_end (@dt DATETIME)
RETURNS DATETIME
BEGIN
   RETURN DATEADD
          ( ms
          , -3
          , DATEADD(mm, 3, dbo.quarter_begin(@dt))
          );
END
GO

把日期當字串比較#

另一個常見的混淆是把日期轉成字串再比較（PostgreSQL 範例）：

SELECT ...
  FROM sales
 WHERE TO_CHAR(sale_date, 'YYYY-MM-DD') = '1970-01-01'

問題依然是轉換了日期欄位。這類條件常源自一種誤解：以為只能傳數字和字串給資料庫。

若做不到，那就轉換搜尋詞、而不是資料表欄位：

SELECT ...
  FROM sales
 WHERE sale_date = TO_DATE('1970-01-01', 'YYYY-MM-DD')

這段查詢能使用 SALE_DATE 上的普通索引，而且只轉換輸入字串一次；前一種寫法則必須把表中所有日期都轉換過才能比較。

無論改用繫結參數還是轉換比較的另一邊，若 SALE_DATE 帶有時間部分，都很容易引入 bug。此時必須使用明確的範圍條件：

SELECT ...
  FROM sales
 WHERE sale_date >= TO_DATE('1970-01-01', 'YYYY-MM-DD')
   AND sale_date <  TO_DATE('1970-01-01', 'YYYY-MM-DD')
                    + INTERVAL '1' DAY

比較日期時，永遠優先考慮明確的範圍條件。

sale_date LIKE SYSDATE

filter( INTERNAL_FUNCTION(SALE_DATE)
   LIKE TO_CHAR(SYSDATE@!))

數值字串#

數值字串（numeric string）是指存放在文字欄位中的數字。這是很糟的做法，但只要你一致地把它當字串處理，索引倒不會自動失效：

SELECT ... FROM ... WHERE numeric_string = '42'

但如果拿數字來比較，資料庫就無法把這個條件當作存取述詞：

SELECT ... FROM ... WHERE numeric_string = 42

注意少了引號。有些資料庫會拋錯（如 PostgreSQL），但許多資料庫只是悄悄加上隱式型別轉換，變成：

SELECT ... FROM ... WHERE TO_NUMBER(numeric_string) = 42

問題與前面相同，解法也相同：不要轉換資料表欄位，要轉換搜尋詞。

SELECT ... FROM ... WHERE numeric_string = TO_CHAR(42)

為什麼資料庫不自動這樣做？#

因為字串轉數字的結果永遠明確，反過來卻不然。一個數字格式化成文字後，可能帶有空格、標點與前導零——同一個值有多種寫法：

42
042
0042
00042
...

資料庫無從得知 NUMERIC_STRING 欄位使用哪種數字格式，於是選擇反方向：把字串轉成數字——這是明確的轉換。

使用數值字串通常麻煩重重：

• 隱式轉換造成效能問題。
• 有踩到轉換錯誤的風險——只要表中存了一筆無效數字，即使是 where 子句完全不含函式的瑣碎查詢，也可能因轉換錯誤而中止。

反過來則沒有這個問題：

SELECT ... FROM ... WHERE numeric_number = '42'

資料庫會一致地把字串轉成數字，不會對（可能被索引的）欄位套用函式，普通索引照樣有效。不過你仍然可能用錯誤的方式手動轉換：

SELECT ... FROM ... WHERE TO_CHAR(numeric_number) = '42'

組合欄位#

這一節談的是影響串接索引的一種常見混淆。

第一個例子又和日期時間有關，只是方向相反。以下 MySQL 查詢把日期欄與時間欄組合起來，對兩者一併套用範圍篩選（選出最近 24 小時的記錄）：

SELECT ...
  FROM ...
 WHERE ADDTIME(date_column, time_column)
     > DATE_ADD(now(), INTERVAL -1 DAY)

這段查詢無法正確使用 (DATE_COLUMN, TIME_COLUMN) 上的串接索引，因為搜尋的對象不是被索引的欄位，而是衍生資料。

解法依序考慮：

1. 改用同時含日期與時間的型別（如 MySQL 的 DATETIME），就能不套函式直接使用該欄位。但面臨此問題時，往往無法改動資料表。
2. 函式索引（若資料庫支援）——但帶有前述所有缺點，而且 MySQL 根本沒這選項。
3. 加上冗餘條件——見下。

用冗餘條件換回存取述詞#

即使如此，仍可以改寫查詢，讓資料庫至少部分地以存取述詞使用串接索引。做法是為 DATE_COLUMN 加上一個額外條件：

WHERE ADDTIME(date_column, time_column)
    > DATE_ADD(now(), INTERVAL -1 DAY)
  AND date_column
   >= DATE(DATE_ADD(now(), INTERVAL -1 DAY))

這個新條件完全冗餘，卻是對 DATE_COLUMN 的直接篩選，可以作為存取述詞。手法雖不完美，通常已是夠好的近似。

當範圍條件跨越多個欄位時，在最重要（most significant）的欄位上加一個冗餘條件。

在 PostgreSQL 中，更推薦使用「分頁瀏覽結果」一節介紹的 row values 語法。

把日期時間存在文字欄位時也能用同樣技巧，但必須採用「字典序即時間序」的格式——例如 ISO 8601 建議的 YYYY-MM-DD HH:MM:SS：

SELECT ...
  FROM ...
 WHERE date_string || time_string
     > TO_CHAR(sysdate - 1, 'YYYY-MM-DD HH24:MI:SS')
   AND date_string
    >= TO_CHAR(sysdate - 1, 'YYYY-MM-DD')

「對多個欄位套用範圍條件」的問題會在「分頁瀏覽結果」一節再次出現，屆時也會用同樣的近似手法緩解。

反過來：刻意混淆#

有時我們遇到相反的情況——刻意混淆某個條件，讓它不能再作為存取述詞。前面討論繫結參數對 LIKE 條件的影響時已碰過這個問題：

SELECT last_name, first_name, employee_id
  FROM employees
 WHERE subsidiary_id = ?
   AND last_name LIKE ?

假設 SUBSIDIARY_ID 與 LAST_NAME 上各有一個索引，哪一個對這段查詢更好？在不知道萬用字元位置的情況下，這問題無法給出可靠答案——最佳化工具只能「猜」。

若你確知必定有前導萬用字元，可以刻意混淆 LIKE 條件，讓最佳化工具不再考慮 LAST_NAME 索引：

SELECT last_name, first_name, employee_id
  FROM employees
 WHERE subsidiary_id = ?
   AND last_name || '' LIKE ?

只要在 LAST_NAME 後面串接一個空字串就夠了。

這是最後手段，非絕對必要時不要使用。

「聰明的邏輯」#

SQL 資料庫的關鍵特性之一，就是支援臨時查詢：新查詢隨時可以執行。這之所以可能，是因為查詢最佳化工具在執行期運作——收到敘述後立即分析並產生合理的執行計畫，而執行期最佳化的開銷可以靠繫結參數降到最低。

結論是：資料庫本來就是為動態 SQL 最佳化的——需要時就用它。

然而有一種廣泛流傳的做法，是為了避免動態 SQL 而改用靜態 SQL，通常出於「動態 SQL 很慢」的迷思。在使用共用執行計畫快取的資料庫（DB2、Oracle、SQL Server）上，這種做法弊遠大於利。

反模式示範#

想像一個應用程式要查詢 EMPLOYEES 表，允許以子公司 ID、員工 ID、姓氏（大小寫不敏感）的任意組合搜尋。用「聰明」的邏輯，確實能寫出一段涵蓋所有情況的查詢：

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
 WHERE ( subsidiary_id    = :sub_id OR :sub_id IS NULL )
   AND ( employee_id      = :emp_id OR :emp_id IS NULL )
   AND ( UPPER(last_name) = :name   OR :name   IS NULL )

所有可能的篩選運算式都靜態寫死在敘述中；不需要某個篩選時，就傳 NULL 進去，靠 OR 邏輯把該條件停用。

這是一段完全合理的 SQL，NULL 的用法甚至符合 SQL 三值邏輯的定義。

資料庫無法為特定篩選最佳化執行計畫，因為任何篩選都可能在執行期被抵銷掉。它只能為最壞情況——所有篩選都被停用——做準備：

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT  |           |    2 |  478 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES |    2 |  478 |
----------------------------------------------------
Predicate Information:
1 - filter((:NAME IS NULL OR UPPER("LAST_NAME")=:NAME)
       AND (:EMP_ID IS NULL OR "EMPLOYEE_ID"=:EMP_ID)
       AND (:SUB_ID IS NULL OR "SUBSIDIARY_ID"=:SUB_ID))

結果就是：即使每個欄位都有索引，資料庫仍然做全表掃描。

問題不在於資料庫解不開這套「聰明」邏輯。它之所以產生通用計畫，是因為使用了繫結參數、要讓計畫可被快取重用。若不用繫結參數、把實際值寫進 SQL，最佳化工具就會為當下生效的篩選挑出正確索引（INDEX RANGE SCAN EMP_UP_NAME，成本 2）——但這不是解法，只證明了資料庫解得開這些條件。

使用字面值會讓應用程式暴露於 SQL 注入攻擊，也會因最佳化開銷上升而造成效能問題。

正解：動態 SQL#

動態查詢的顯然解法就是動態 SQL。依照 KISS 原則，只告訴資料庫你當下需要什麼，別的都不要說：

SELECT first_name, last_name, subsidiary_id, employee_id
  FROM employees
 WHERE UPPER(last_name) = :name

需要動態 where 子句時，就用動態 SQL。

產生動態 SQL 時仍然要用繫結參數——否則「動態 SQL 很慢」的迷思就成真了。

雖然這些啟發式方法能一定程度改善「聰明邏輯」問題，但它們原本是為了處理「繫結參數搭配欄位直方圖與 LIKE 運算式」的問題而設計的。

取得最佳執行計畫最可靠的方法，是避免在 SQL 敘述中放入不必要的篩選。

數學運算#

還有一類混淆同樣「聰明」，同樣阻止索引被正確使用——它不用邏輯運算式，而是用計算。

下面這段能使用 NUMERIC_NUMBER 上的索引嗎？

SELECT numeric_number
  FROM table_name
 WHERE numeric_number - 1000 > ?

那這段呢——它能使用 A 與 B 上的索引嗎（順序由你決定）？

SELECT a, b
  FROM table_name
 WHERE 3*a + 5 = b

換個角度想：如果你在開發 SQL 資料庫，你會加進一個方程式求解器嗎？多數資料庫廠商的回答就是「不會」——因此兩個例子都用不上索引。

同樣地，你也能用數學運算刻意混淆條件（就像先前對全文 LIKE 搜尋所做的），加個零就夠了：

SELECT numeric_number
  FROM table_name
 WHERE numeric_number + 0 = ?

用移項換回索引#

不過，只要聰明地運用計算、像解方程式一樣改寫 where 子句，就能用函式索引把這些運算式索引起來：

SELECT a, b
  FROM table_name
 WHERE 3*a - b = -5

我們把資料表的參照移到一邊、常數移到另一邊，接著就能為等式左側建立函式索引：

CREATE INDEX math ON table_name (3*a - b)