SQL 的 NULL 經常造成困惑。它的基本概念——表示缺失的資料——相當簡單，但仍有些怪癖，例如必須用 IS NULL 而不是 = NULL。

而 Oracle 資料庫還有額外的 NULL 怪異之處，原因有二：

  • 它並不總是照標準的要求處理 NULL。
  • 它對索引中的 NULL 有一套非常「特別」的處理方式。

Oracle 把空字串當成 NULL#

SQL 標準不把 NULL 定義為一個值，而是「缺失或未知的值」的佔位符——因此任何值都不可能是 NULL。但 Oracle 把空字串視為 NULL：

   SELECT '0 IS NULL???' AS "what is NULL?" FROM dual
    WHERE  0 IS NULL
UNION ALL
   SELECT '0 is not null' FROM dual
    WHERE  0 IS NOT NULL
UNION ALL
   SELECT ''''' IS NULL???' FROM dual
    WHERE  '' IS NULL
UNION ALL
   SELECT ''''' is not null' FROM dual
    WHERE  '' IS NOT NULL;
what is NULL?
--------------
0 is not null
'' IS NULL???

更混亂的是，還有一種情況 Oracle 反過來把 NULL 當成空字串：

SELECT dummy
     , dummy || ''
     , dummy || NULL
  FROM dual;
D D D
- - -
X X X

把恆為 'X' 的 DUMMY 欄位與 NULL 串接，照理應該回傳 NULL。

索引 NULL#

若一列在所有被索引欄位上都是 NULL，Oracle 就不會把它放進索引。這代表每個索引其實都是部分索引，等同於帶著這樣的 where 子句：

CREATE INDEX idx
          ON tbl (A, B, C, ...)
       WHERE A IS NOT NULL
          OR B IS NOT NULL
          OR C IS NOT NULL;

以只含 DATE_OF_BIRTH 一欄的 EMP_DOB 索引為例。以下 insert 沒有設定 DATE_OF_BIRTH，預設為 NULL，該筆記錄因此不會被加進索引：

INSERT INTO employees ( subsidiary_id, employee_id
                      , first_name , last_name
                      , phone_number)
               VALUES ( ?, ?, ?, ?, ? );

結果就是索引無法支援查詢 DATE_OF_BIRTH IS NULL 的記錄——只能全表掃描：

----------------------------------------------------
| Id | Operation         | Name      | Rows | Cost |
----------------------------------------------------
| 0 | SELECT STATEMENT  |           |    1 |  477 |
|* 1 | TABLE ACCESS FULL| EMPLOYEES |    1 |  477 |
----------------------------------------------------
Predicate Information:
   1 - filter("DATE_OF_BIRTH" IS NULL)

串接索引：只要一欄不是 NULL 就會被收錄#

CREATE INDEX demo_null
          ON employees (subsidiary_id, date_of_birth);

由於 SUBSIDIARY_ID 不是 NULL，該列會被加進索引。這個索引因而能支援「某子公司中沒有生日資料的員工」查詢：

SELECT first_name, last_name
  FROM employees
 WHERE subsidiary_id = ?
   AND date_of_birth IS NULL;
--------------------------------------------------------------
| Id | Operation                   | Name      | Rows | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT             |           |    1 |    2 |
| 1 | TABLE ACCESS BY INDEX ROWID  | EMPLOYEES |    1 |    2 |
|* 2 |   INDEX RANGE SCAN          | DEMO_NULL |    1 |    1 |
--------------------------------------------------------------
Predicate Information:
   2 - access("SUBSIDIARY_ID"=TO_NUMBER(?)
          AND "DATE_OF_BIRTH" IS NULL)

注意索引涵蓋了整個 where 子句：所有篩選都作為存取述詞使用。

讓 NULL 也能被索引的技巧#

把這個概念推廣到原本的查詢：要找出所有 DATE_OF_BIRTH IS NULL 的記錄，DATE_OF_BIRTH 必須是索引的最左欄才能作為存取述詞。查詢本身雖然不需要第二欄，但我們仍加上一個永遠不可能為 NULL 的欄位，以確保索引包含所有列。

可以用任何具 NOT NULL 約束的欄位（如 SUBSIDIARY_ID），也可以用一個永遠不會是 NULL 的常數運算式：

DROP   INDEX emp_dob;
CREATE INDEX emp_dob ON employees (date_of_birth, '1');

技術上這是一個函式索引。這個例子也推翻了「Oracle 無法索引 NULL」的迷思。

加上一個不可能為 NULL 的欄位，就能像索引任何值一樣索引 NULL。

NOT NULL 約束#

要在 Oracle 中索引 IS NULL 條件，索引就必須有一個永遠不會是 NULL 的欄位。

光是「目前沒有 NULL 條目」還不夠。資料庫必須確定永遠不可能有 NULL 條目，否則它只能假設表中存在不在索引裡的列。

以下索引只有在 LAST_NAME 具備 NOT NULL 約束時才支援該查詢：

DROP INDEX emp_dob;
CREATE INDEX emp_dob_name
          ON employees (date_of_birth, last_name);

SELECT * FROM employees WHERE date_of_birth IS NULL;
---------------------------------------------------------------
|Id |Operation                   | Name         | Rows | Cost |
---------------------------------------------------------------
| 0 |SELECT STATEMENT            |              |    1 |    3 |
| 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |    1 |    3 |
|*2 | INDEX RANGE SCAN           | EMP_DOB_NAME |    1 |    2 |
---------------------------------------------------------------

移除該約束，索引立刻對這段查詢失效，退回全表掃描（成本 477）：

ALTER TABLE employees MODIFY last_name NULL;

在 Oracle 中，缺少 NOT NULL 約束可能導致索引用不上——尤其是 count(*) 這類查詢。

使用者自訂函式會弄丟 NOT NULL 屬性#

除了 NOT NULL 約束外，資料庫也知道前一節那種常數運算式不可能是 NULL。但建在使用者自訂函式上的索引，並不會為該運算式賦予 NOT NULL 約束：

CREATE OR REPLACE FUNCTION blackbox(id IN NUMBER) RETURN NUMBER
DETERMINISTIC
IS BEGIN
   RETURN id;
END;

DROP INDEX emp_dob_name;
CREATE INDEX emp_dob_bb
    ON employees (date_of_birth, blackbox(employee_id));

SELECT * FROM employees WHERE date_of_birth IS NULL;
-- → TABLE ACCESS FULL，成本 477

函式取名 BLACKBOX 正是要強調：最佳化工具完全不知道這個函式做了什麼。我們看得出它把輸入值原封不動傳回，但對資料庫而言它只是個回傳數字的函式——參數的 NOT NULL 屬性就此遺失。索引其實包含了所有列，但資料庫不知道，於是無法使用它。

解法一：在查詢中明示。 若你知道該函式永不回傳 NULL，可以把這件事寫進查詢：

SELECT * FROM employees
 WHERE date_of_birth IS NULL
   AND blackbox(employee_id) IS NOT NULL;
-- → INDEX RANGE SCAN EMP_DOB_BB，成本 3

這個額外條件恆為真、不改變結果，但 Oracle 會辨識出你只查詢「依定義必定在索引中」的列。

解法二：虛擬欄位加約束。 可惜沒有辦法把函式標記為「永不回傳 NULL」，但可以把函式呼叫移到虛擬欄位（11g 起支援），再對該欄位加上 NOT NULL 約束：

ALTER TABLE employees ADD bb_expression
      GENERATED ALWAYS AS (blackbox(employee_id)) NOT NULL;
DROP   INDEX emp_dob_bb;
CREATE INDEX emp_dob_bb
     ON employees (date_of_birth, bb_expression);

內建函式則能保留 NOT NULL 屬性#

Oracle 知道某些內建函式只有在輸入為 NULL 時才回傳 NULL：

CREATE INDEX emp_dob_upname
    ON employees (date_of_birth, upper(last_name));

SELECT * FROM employees WHERE date_of_birth IS NULL;
-- → INDEX RANGE SCAN EMP_DOB_UPNAME，成本 3

UPPER 保留了 LAST_NAME 欄位的 NOT NULL 屬性。但一旦移除該約束，索引同樣失效退回全表掃描。

模擬部分索引#

Oracle 處理索引中 NULL 的怪異方式，反過來可以用來模擬部分索引——只要讓「不該被索引的列」在索引運算式上回傳 NULL 即可。

以模擬下列部分索引為例：

CREATE INDEX messages_todo
          ON messages (receiver)
       WHERE processed = 'N'

第一步，寫一個「只在 PROCESSED 為 'N' 時回傳 RECEIVER」的函式（必須是 DETERMINISTIC 才能用於索引定義）：

CREATE OR REPLACE
FUNCTION pi_processed(processed CHAR, receiver NUMBER)
RETURN NUMBER
DETERMINISTIC
AS BEGIN
   IF processed IN ('N') THEN
      RETURN receiver;
   ELSE
      RETURN NULL;
   END IF;
END;
/

第二步，建立只包含 PROCESSED='N' 那些列的索引：

CREATE INDEX messages_todo
          ON messages (pi_processed(processed, receiver));

要用上這個索引，查詢中必須使用被索引的運算式：

SELECT message
  FROM messages
 WHERE pi_processed(processed, receiver) = ?
延伸：部分索引的另一種模擬方式

自 11g 起，Oracle 還有第二種——同樣嚇人的——模擬部分索引手法：刻意使用一個損壞的索引分割區（broken index partition），搭配 SKIP_UNUSABLE_INDEX 參數。

Backward 部分索引 被混淆的條件 Forward