LAST_NAME 上的索引大幅改善了效能，但它要求你搜尋時使用與資料庫中儲存完全相同的大小寫。本節說明如何在不犧牲效能的前提下解除這個限制。

MySQL 5.6 不支援下述的函式索引。替代方案「虛擬欄位（virtual column）」原訂於 MySQL 6.0 推出，最終只在 MariaDB 5.2 中實現。

用 UPPER / LOWER 做大小寫不敏感搜尋#

在 where 子句中忽略大小寫非常簡單，例如把比較的兩邊都轉成大寫：

SELECT first_name, last_name, phone_number
  FROM employees
 WHERE UPPER(last_name) = UPPER('winand');

無論搜尋詞或 LAST_NAME 欄位使用何種大小寫，UPPER 都能讓它們如預期匹配。

另一種大小寫不敏感匹配的方式是使用不同的定序（collation）。SQL Server 與 MySQL 的預設定序不區分大小寫。

邏輯合理，執行計畫卻不合理#

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT  |           |   10 |  477 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES |   10 |  477 |
----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(UPPER("LAST_NAME")='WINAND')

老朋友全表掃描又回來了。雖然 LAST_NAME 上有索引，卻用不上——因為搜尋的對象不是 LAST_NAME，而是 UPPER(LAST_NAME)。從資料庫的角度看，這是完全不同的東西。

這是我們都可能掉進去的陷阱：我們一眼就看出 LAST_NAME 與 UPPER(LAST_NAME) 的關聯，於是期待資料庫也「看得見」。但最佳化工具眼中的世界更像這樣：

SELECT first_name, last_name, phone_number
  FROM employees
 WHERE BLACKBOX(...) = 'WINAND';

UPPER 只是個黑盒子。函式的參數毫不相關，因為參數與結果之間沒有通則性的關係。

把函式名稱換成 BLACKBOX，就能理解最佳化工具的視角。

解法：函式索引（function-based index）#

要支援這段查詢，就需要一個涵蓋實際搜尋詞的索引——不是建在 LAST_NAME 上，而是建在 UPPER(LAST_NAME) 上：

CREATE INDEX emp_up_name
    ON employees (UPPER(last_name));

定義中包含函式或運算式的索引，稱為函式索引（FBI, function-based index）。它不直接把欄位資料複製進索引，而是先套用函式、再把結果放進索引——因此索引中儲存的是全大寫的姓氏。

只要 SQL 敘述中出現與索引定義完全相同的運算式，資料庫就能使用函式索引：

--------------------------------------------------------------
|Id |Operation                   | Name        | Rows | Cost |
--------------------------------------------------------------
| 0 |SELECT STATEMENT            |             |  100 |   41 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |  100 |   41 |
|*2 | INDEX RANGE SCAN           | EMP_UP_NAME |   40 |    1 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
  2 - access(UPPER("LAST_NAME")='WINAND')

這就是第 1 章描述的一般 INDEX RANGE SCAN：走訪 B-tree、沿葉節點鏈前進。函式索引沒有專屬的操作或關鍵字。

有時 ORM 工具會在開發者不知情的情況下使用 UPPER 與 LOWER。例如 Hibernate 在做大小寫不敏感搜尋時，會隱式注入 LOWER。

留意最佳化工具的估算#

上面的執行計畫仍與前一節不同：列數估算偏高。特別奇怪的是，最佳化工具預期從表中抓取的列數（100）竟然多於 INDEX RANGE SCAN 產出的列數（40）——這不可能。這類自相矛盾的估算，通常代表統計資訊有問題。本例的原因是 Oracle 建立新索引時不會更新資料表統計。

更新統計後，估算就準確多了：

--------------------------------------------------------------
|Id |Operation                   | Name        | Rows | Cost |
--------------------------------------------------------------
| 0 |SELECT STATEMENT            |             |    1 |    3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES   |    1 |    3 |
|*2 | INDEX RANGE SCAN           | EMP_UP_NAME |    1 |    1 |
--------------------------------------------------------------

即使本例中更新統計並未改善執行效能（索引本來就被正確使用），檢查最佳化工具的估算永遠是好習慣。每個操作處理的列數（cardinality estimate）是特別重要的數字，SQL Server 與 PostgreSQL 的執行計畫中同樣會顯示。

針對運算式與欄位群組的所謂「擴充統計（extended statistics）」自 Oracle 11g 起提供。

SQL Server：用計算欄位替代#

SQL Server 不支援上述形式的函式索引，但提供計算欄位（computed column）。先加上計算欄位，再對它建索引：

ALTER TABLE employees ADD last_name_up AS UPPER(last_name);
CREATE INDEX emp_up_name ON employees (last_name_up);

只要敘述中出現該運算式，SQL Server 就能使用這個索引——不需要改寫查詢去引用計算欄位。

使用者自訂函式#

函式索引是非常通用的手法：除了 UPPER 這類函式，你也可以索引 A + B 這樣的運算式，甚至在索引定義中使用使用者自訂函式。

但有一個重要的例外：索引定義中不能引用當前時間，無論直接或間接。

CREATE FUNCTION get_age(date_of_birth DATE)
RETURN NUMBER
AS
BEGIN
   RETURN
     TRUNC(MONTHS_BETWEEN(SYSDATE, date_of_birth)/12);
END;
/

GET_AGE 用當前日期（SYSDATE）從生日算出年齡。你可以在查詢的任何部分使用它：

SELECT first_name, last_name, get_age(date_of_birth)
  FROM employees
 WHERE get_age(date_of_birth) = 42;

用函式索引優化這段查詢是很自然的念頭，但你不能把 GET_AGE 用在索引定義中，因為它不具決定性（deterministic）——函式呼叫的結果並非完全由參數決定。只有「相同參數永遠回傳相同結果」的函式才能被索引。

限制背後的道理很簡單：插入新列時，資料庫呼叫函式並把結果存進索引，它就一直待在那裡，不會改變。沒有任何週期性程序會更新索引；只有當 update 改動了生日，索引中的年齡才會更新。等到下一個生日過後，索引裡存的年齡就錯了。

PostgreSQL 與 Oracle 除了要求函式具決定性外，還要求在索引中使用時明確宣告：Oracle 用 DETERMINISTIC，PostgreSQL 用 IMMUTABLE。

PostgreSQL 與 Oracle 信任這些宣告——也就是信任開發者。你大可把 GET_AGE 宣告為 deterministic 並用在索引定義中，但無論怎麼宣告都不會如預期運作：索引裡存的年齡不會隨歲月增加，員工在索引裡永遠不會變老。

其他無法被索引的函式還包括亂數產生器，以及依賴環境變數的函式。

過度索引#

如果函式索引對你是新概念，你可能會忍不住把所有東西都索引起來——但這其實是最不該做的事。每個索引都帶來持續的維護成本，而函式索引尤其麻煩，因為它讓「建出冗餘索引」變得太容易。

前面的大小寫不敏感搜尋，用 LOWER 也能實作：

SELECT first_name, last_name, phone_number
  FROM employees
 WHERE LOWER(last_name) = LOWER('winand');

單一索引無法同時支援這兩種忽略大小寫的寫法。當然可以再為 LOWER(last_name) 建第二個索引，但那代表每次 insert、update、delete 資料庫都得維護兩個索引（見第 8 章「修改資料」）。