Oracle中NULL的注意事项和用法

最近在论坛上经常看到，很多人提出和NULL有关的问题。NULL其实是数据库中特有的类型，Oracle中很多容易出现的错误都是和NULL有关的。

打算简单的总结一下NULL的相关知识。

这一篇主要打算NULL的基础概念和由来，并介绍对NULL的操作的基本特点。

 

NULL是数据库中特有的数据类型，当一条记录的某个列为NULL，则表示这个列的值是未知的、是不确定的。既然是未知的，就有无数种的可能性。因此，NULL并不是一个确定的值。

这是NULL的由来、也是NULL的基础，所有和NULL相关的操作的结果都可以从NULL的概念推导出来。

判断一个字段是否为NULL，应该用IS NULL或IS NOT NULL，而不能用‘=’。对NULL的判断只能定性，既是不是NULL（IS NULL/IS NOT NULL），而不能定值。简单的说，由于NULL存在着无数的可能，因此两个NULL不是相等的关系，同样也不能说两个NULL就不相等，或者比较两个 NULL的大小，这些操作都是没有意义，得不到一个确切的答案的。因此，对NULL的=、!=、>、<、>=、<=等操作的结果 都是未知的，也就算说，这些操作的结果仍然是NULL。

同理，对NULL进行+、-、*、/等操作的结果也是未知的，所以也是NULL。

所以，很多时候会这样总结NULL，除了IS NULL、IS NOT NULL以外，对NULL的任何操作的结果还是NULL。

上面这句话总结的很精辟，而且很好记，所以很多时候人们只记得这句话，而忘了这句话是如何得到的。其实只要清楚NULL的真正含义，在处理NULL的时候就不会出错。

说了怎么多，来看一个经典的例子：

SQL> CREATE OR REPLACE PROCEDURE P1 (P_IN IN NUMBER) AS
2 BEGIN
3 IF P_IN >= 0 THEN
4 DBMS_OUTPUT.PUT_LINE('TRUE');
5 ELSE
6 DBMS_OUTPUT.PUT_LINE('FALSE');
7 END IF;
8 END;
9 /

过程已创建。

SQL> CREATE OR REPLACE PROCEDURE P2 (P_IN IN NUMBER) AS
2 BEGIN
3 IF P_IN < 0 THEN
4 DBMS_OUTPUT.PUT_LINE('FALSE');
5 ELSE
6 DBMS_OUTPUT.PUT_LINE('TRUE');
7 END IF;
8 END;
9 /

过程已创建。

上面两个过程是否是等价的？对于熟悉C或JAVA的开发人员来说，可能认为二者是等价的，但是在数据库中，则还要考虑到NULL的情况。

当输入为NULL时，可以看到上面两个过程不同的输出：

SQL> SET SERVEROUT ON
SQL> EXEC P1(NULL)
FALSE

PL/SQL 过程已成功完成。

SQL> EXEC P2(NULL)
TRUE

PL/SQL 过程已成功完成。

输入为NULL时，上面两个过程中的判断的结果都是一样的，不管是NULL >= 0还是NULL < 0结果都是未知，所以两个判断的结果都是NULL。最终，在屏幕上输出的都是ELSE后面跟的输出值。

由于NULL所具有的特殊性，在处理数据库相关问题时应该对NULL的情况额外考虑，否则很容易造成错误。

由于引入了NULL，在处理逻辑过程中一定要考虑NULL的情况。同样的，数据库中的布尔值的处理，也是需要考虑NULL的情况，这使得布尔值从原来的TRUE、FALSE两个值变成了TRUE、FALSE和NULL三个值。

下面是TRUE和FALSE两种情况进行布尔运算的结果：

AND操作：

AND	TRUE	FALSE
TRUE	TRUE	FALSE
FALSE	FALSE	FALSE

OR操作：

OR	TRUE	FALSE
TRUE	TRUE	TRUE
FALSE	TRUE	FALSE

上面是熟悉的TRUE和FALSE两个值进行布尔运算的结果，如果加上一个NULL的情况会怎样？NULL的布尔运算是否会像NULL的算术运算那样结果都是NULL呢？下面通过一个过程来进行说明：

SQL> SET SERVEROUT ON SIZE 100000
SQL> DECLARE
2 TYPE T_BOOLEAN IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
3 V_BOOL1 T_BOOLEAN;
4 V_BOOL2 T_BOOLEAN;
5
6 PROCEDURE P(P_IN1 BOOLEAN, P_IN2 BOOLEAN, P_OPERATOR IN VARCHAR2) AS
7 V_RESULT BOOLEAN;
8 BEGIN
9 IF P_IN1 IS NULL THEN
10 DBMS_OUTPUT.PUT('NULL ');
11 ELSIF P_IN1 THEN
12 DBMS_OUTPUT.PUT('TRUE ');
13 ELSE
14 DBMS_OUTPUT.PUT('FALSE ');
15 END IF;
16
17 IF P_OPERATOR = 'AND' THEN
18 DBMS_OUTPUT.PUT('AND ');
19 V_RESULT := P_IN1 AND P_IN2;
20 ELSIF P_OPERATOR = 'OR' THEN
21 DBMS_OUTPUT.PUT('OR ');
22 V_RESULT := P_IN1 OR P_IN2;
23 ELSE
24 RAISE_APPLICATION_ERROR('-20000', 'INPUT PARAMETER P_OPERATOR ERROR');
25 END IF;
26
27 IF P_IN2 IS NULL THEN
28 DBMS_OUTPUT.PUT('NULL');
29 ELSIF P_IN2 THEN
30 DBMS_OUTPUT.PUT('TRUE');
31 ELSE
32 DBMS_OUTPUT.PUT('FALSE');
33 END IF;
34
37
39
41 END IF;
42 DBMS_OUTPUT.NEW_LINE;
43 END;
44
45 BEGIN
46 V_BOOL1(1) := TRUE;
47 V_BOOL1(2) := FALSE;
48 V_BOOL1(3) := NULL;
49 V_BOOL2 := V_BOOL1;
50 FOR I IN 1..V_BOOL1.COUNT LOOP
51 FOR J IN 1..V_BOOL2.COUNT LOOP
52 P(V_BOOL1(I), V_BOOL2(J), 'AND');
53 P(V_BOOL1(I), V_BOOL2(J), 'OR');
54 END LOOP;
55 END LOOP;
56 END;
57 /
TRUE AND TRUE:TRUE
TRUE OR TRUE:TRUE
TRUE AND FALSE:FALSE
TRUE OR FALSE:TRUE
TRUE AND NULL:NULL
TRUE OR NULL:TRUE
FALSE AND TRUE:FALSE
FALSE OR TRUE:TRUE
FALSE AND FALSE:FALSE
FALSE OR FALSE:FALSE
FALSE AND NULL:FALSE
FALSE OR NULL:NULL
NULL AND TRUE:NULL
NULL OR TRUE:TRUE
NULL AND FALSE:FALSE
NULL OR FALSE:NULL
NULL AND NULL:NULL
NULL OR NULL:NULL

PL/SQL 过程已成功完成。

由于NULL是未知，所以NULL AND NULL、NULL OR NULL、NULL AND TRUE和NULL OR FALSE的值都是未知的，这些的结果仍然是NULL。

那么为什么NULL AND FALSE和NULL OR TRUE得到了一个确定的结果呢？仍然从NULL的概念来考虑。NULL是未知的，但是目前NULL的类型是布尔类型，因此NULL只有可能是TRUE或者FALSE中的一个。

而根据前面的表格，TRUE AND FALSE和FALSE AND FALSE的结果都是FALSE，也就是说不管NULL的值是TRUE还是FALSE，它与FALSE进行AND的结果一定是FALSE。

同样的道理，TRUE AND TRUE和FALSE AND TRUE的结果都是TRUE，所以不管NULL取何值，NULL和TRUE的OR的结果都是TRUE。

AND操作图表变为：

AND	TRUE	FALSE	NULL
TRUE	TRUE	FALSE	NULL
FALSE	FALSE	FALSE	FALSE
NULL	NULL	FALSE	NULL

OR操作图表变为：

OR	TRUE	FALSE	NULL
TRUE	TRUE	TRUE	TRUE
FALSE	TRUE	FALSE	NULL
NULL	TRUE	NULL	NULL

最后，仍然来看一个例子：

SQL> SELECT * FROM TAB;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
PLAN_TABLE TABLE
T TABLE
T1 TABLE
T2 TABLE
T3 TABLE
TEST TABLE
TEST1 TABLE
TEST_CORRUPT TABLE
T_TIME TABLE

已选择9行。

SQL> SELECT * FROM TAB WHERE TNAME IN ('T', 'T1', NULL);

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
T TABLE
T1 TABLE

SQL> SELECT * FROM TAB WHERE TNAME NOT IN ('T', 'T1', NULL);

未选定行

对于IN和NOT IN与NULL的关系前面并没有说明，不过可以对其进行简单的变形：

TNAME IN (‘T’, ‘T1’, NULL) < = > TNAME = ‘T’ OR TNAME = ‘T1’ OR TNAME = NULL

根据前面的结果，当查询到T或T1这两条记录时，WHERE条件相当于TRUE AND FALSE AND NULL，其结果是TRUE，因此返回了两条记录。

TNAME NOT IN (‘T’, ‘T1’, NULL) < = > TNAME != ‘T’ AND TNAME != ‘T1’ AND TNAME != NULL。

WHERE条件相当于TRUE AND TRUE AND NULL，或TRUE AND FALSE AND NULL，其最终结果是NULL或者FALSE，所以，查询不会返回记录。

对于TRUE和FALSE的NOT运算很简单，NOT TRUE=FALSE，NOT FALSE=TRUE，那么如果包含NULL的情况呢，首先还是用事实来说话：

SQL> SET SERVEROUT ON SIZE 100000
SQL> DECLARE
2 TYPE T_BOOLEAN IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;
3 V_BOOL T_BOOLEAN;
4
5 PROCEDURE P(P_IN BOOLEAN) AS
6 V_RESULT BOOLEAN;
7 BEGIN
8 IF P_IN IS NULL THEN
9 DBMS_OUTPUT.PUT('NOT NULL');
10 ELSIF P_IN THEN
11 DBMS_OUTPUT.PUT('NOT TRUE');
12 ELSE
13 DBMS_OUTPUT.PUT('NOT FALSE');
14 END IF;
15
16 V_RESULT := NOT P_IN;
17
20
22
24 END IF;
25 DBMS_OUTPUT.NEW_LINE;
26 END;
27
28 BEGIN
29 V_BOOL(1) := TRUE;
30 V_BOOL(2) := FALSE;
31 V_BOOL(3) := NULL;
32 FOR I IN 1..V_BOOL.COUNT LOOP
33 P(V_BOOL(I));
34 END LOOP;
35 END;
36 /
NOT TRUE:FALSE
NOT FALSE:TRUE
NOT NULL:NULL

PL/SQL 过程已成功完成。

现在我们看到了一个很有趣的结果，NOT NULL的结果仍然是NULL。可能很多人对此并不理解。下面还是从NULL的基本概念来解释。

NULL表示的是未知的含义，而增加一个NOT操作后，并不能使NULL变为一个确定的值，如果是TRUE，NOT TRUE将变为FALSE，如果是FALSE，NOT FALSE将变为TRUE，所有，即使进行了NOT操作，NULL本身的不确定性是仍然存在的。这就是最终结果仍然是NULL的原因。

这里需要注意：这个NOT NULL是一个布尔操作，要和SQL中的NOT NULL约束进行区分。NOT NULL约束是一个定性的描述，只是表示列中的数据不允许为NULL。而这里的布尔操作，却是在进行求值，要得到对NULL取非的结果，所以仍然得到NULL。

NOT TRUE	NOT FALSE	NOT NULL
FALSE	TRUE	NULL

以前我总说空字符串’’等价于NULL，但是有些人喜欢钻牛角尖，所以我改一下说法，空字符串’’是NULL的字符类型的表现格式。

也许有人会认为，NULL就是NULL，本身没有类型的一说，但是我认为，NULL还是有类型的，只不过不同类型的NULL都用相同的关键字NULL来表示。而且，NULL本身也可以转化为任意类型的数据，因此给人的感觉是NULL没有数据类型。

其实NULL不但有数据类型，还有默认的数据类型，那就是字符类型。至于这个答案是如何推断出来的，请看：http://yangtingkun.itpub.net/post/468/50132

不过上面说的这个默认的数据类型是在极限的情况下测试出来的，如果只是给出一个NULL，那么它是可以代表任意的类型的。

证明空字符串就是NULL是很容易的：

SQL> SELECT 1 FROM DUAL WHERE '' = '';

未选定行

SQL> SELECT 1 FROM DUAL WHERE '' IS NULL;

1
----------
1

SQL> SELECT DUMP(''), DUMP(NULL) FROM DUAL;

DUMP DUMP
---- ----
NULL NULL

上面三个SQL语句，任意一个都足以证明空字符串’’就是NULL。

有些人可能会说，既然’’就是NULL，为什么不能进行IS ’’的判断呢?

SQL> SELECT 1 FROM DUAL WHERE '' IS '';
SELECT 1 FROM DUAL WHERE '' IS ''
*第 1 行出现错误:
ORA-00908: 缺失 NULL 关键字

其实从上面的错误信息就可以看到答案。原因就是IS NULL是Oracle的语法，在Oracle运行的时刻’’是NULL，但是现在Oracle还没有运行这句SQL，就由于语法不正确被SQL分析器挡住了。Oracle的语法并不包含IS ’’的写法，所以，这一点并不能称为’’不是NULL的理由。

那么我为什么还要说’’是NULL的字符表示形式呢？因为’’和NULL还确实不完全一样，对于NULL来说，它表示了各种数据类型的NULL值。而对于空字符串’’来说，虽然它也具有NULL的可以任意转化为其他任何数据类型的特点，但是无论是从形式上还是从本质上它都表现出了字符类型的特点。

下面通过一个例子来证明’’本质是字符类型的NULL。

SQL> CREATE OR REPLACE PACKAGE P_TEST_NULL AS
2 FUNCTION F_RETURN (P_IN IN NUMBER) RETURN VARCHAR2;
3 FUNCTION F_RETURN (P_IN IN VARCHAR2) RETURN VARCHAR2;
4 END;
5 /

程序包已创建。

SQL> CREATE OR REPLACE PACKAGE BODY P_TEST_NULL AS
2
3 FUNCTION F_RETURN (P_IN IN NUMBER) RETURN VARCHAR2 AS
4 BEGIN
5 RETURN 'NUMBER';
6 END;
7
8 FUNCTION F_RETURN (P_IN IN VARCHAR2) RETURN VARCHAR2 AS
9 BEGIN
10 RETURN 'VARCHAR2';
11 END;
12
13 END;
14 /

程序包体已创建。

SQL> SELECT P_TEST_NULL.F_RETURN(3) FROM DUAL;

P_TEST_NULL.F_RETURN(3)
------------------------------------------------------------
NUMBER

SQL> SELECT P_TEST_NULL.F_RETURN('3') FROM DUAL;

P_TEST_NULL.F_RETURN('3')
------------------------------------------------------------
VARCHAR2

SQL> SELECT P_TEST_NULL.F_RETURN('') FROM DUAL;

P_TEST_NULL.F_RETURN('')
------------------------------------------------------------
VARCHAR2

SQL> SELECT P_TEST_NULL.F_RETURN(NULL) FROM DUAL;
SELECT P_TEST_NULL.F_RETURN(NULL) FROM DUAL
*第 1 行出现错误:
ORA-06553: PLS-307: 有太多的 'F_RETURN' 声明与此次调用相匹配

从这一点上可以看出’’实际上已经具备了数据类型。所以我将’’表述为空字符串是NULL的字符类型表现形式。

上面一篇文章中，已经从事实的角度证明了空字符就是NULL的字符表现形式。这一篇将试图解释为什么空字符就是NULL。而且准备简单描述一下字符串合并操作||的特殊性。

根据NULL的定义，NULL是不确定、未知的含义，那么为什么字符类型的NULL是一个空字符呢？而且，对于NULL的加、减、乘、除等操作的结果都是NULL，而为什么字符串合并操作||，当输入字符串有一个为空时，不会得到结果NULL。

SQL> SELECT NULL || 'A', 'B' || NULL, NULL || NULL FROM DUAL;

NU ' N
-- - -
A B

上面两个问题需要从NULL的存储格式上解释。Oracle在存储数据时，先是存储这一列的长度，然后存储列数据本身。而对于NULL，只包含一个FF，没有数据部分。简单的说，Oracle用长度FF来表示NULL。

由于Oracle在处理的数据存储的时候尽量避免0的出现，因此，认为这里FF表示的是长度为0也是有一定道理的。或者从另一方面考虑，NULL只有一个长度，而没有数据部分。

而对于字符串来说，不管是长度为0的字符串还是没有任何数据的字符串，所代表的含义都是一个空字符串。从一点上讲，空字符串就是NULL也是有一定的道理的。

如果认为空字符串是字符形式的NULL，那么||操作的结果就不难理解了。

最后需要说明的是，不要将ORACLE里面的空字符串’’与C里面的空字符串””混淆。C里面的空字符串并非不不含任何数据，里面还包含了一个字符串结束符。C语言中的空字符串””对应Oracle中ASCII表中的0值，既CHR(0)。

但CHR(0)是一个确定的值，它显然不是NULL。

SQL> SELECT * FROM DUAL WHERE CHR(0) = CHR(0);

D
-
X

SQL> SELECT * FROM DUAL WHERE CHR(0) IS NULL;

未选定行

前面几篇文章讨论了NULL的数据类型和NULL的运算特点。这里打算简单描述NULL和索引的关系。

如果说NULL类型已经比较容易出错了，那么索引问题就让NULL又一次成为问题的焦点。

大多数人都听说过这样一句话，索引不存储NULL值。这句话其实比不严谨。如果采用比较严谨的方式来说：B树索引不存储索引列全为空的记录。如果把这句话用在单列索引上，就是前面提到的B树索引不存储NULL。

首先索引分为BTREE和BITMAP两种，对于BTREE索引，是不存储NULL值的，而对于BITMAP索引，是存储NULL值的。

而从索引列的个数来划分，索引非为单列索引和复合索引，对于单列索引来说很简单，如果一条记录中这个索引字段为空，那么索引不会保存这条记录的信息。但是对于复合索引，由于存在着多个列，如果某一个索引列不为空，那么索引就会包括这条记录，即使其他所有的所有列都是NULL值。

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

表已创建。

SQL> DESC T
名称 是否为空? 类型
-------------------------------------------- -------- ------------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)

SQL> CREATE INDEX IND_T_OBJECT_ID ON T (OBJECT_ID);

索引已创建。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', CASCADE => TRUE)

PL/SQL 过程已成功完成。

SQL> SET AUTOT ON EXP
SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
50297

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 50297 | 41 (3)| 00:00:01 |
-------------------------------------------------------------------

SQL> SELECT /*+ INDEX(T IND_T_OBJECT_ID) */ COUNT(*) FROM T;

COUNT(*)
----------
50297

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 (3)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T | 50297 | 41 (3)| 00:00:01 |
-------------------------------------------------------------------

Oracle的优化器在确定是否使用索引的时候，第一标准是能否得到一个正确的结果。由于OBJECT_ID是可以为空的，而索引列不包含为空的记录。因此通过索引扫描无法得到一个正确的结果，这就是SELECT COUNT(*) FROM T不会使用OBJECT_ID上的索引的原因。

而对于BITMAP索引，则是另外的情况：

SQL> DROP INDEX IND_T_OBJECT_ID;

索引已删除。

SQL> CREATE BITMAP INDEX IND_B_T_DATA_ID ON T (DATA_OBJECT_ID);

索引已创建。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
50297

执行计划
----------------------------------------------------------
Plan hash value: 3051411170

-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 2 (0)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | BITMAP CONVERSION COUNT| | 50297 | 2 (0)|
| 3 | BITMAP INDEX FULL SCAN| IND_B_T_DATA_ID | | |
-------------------------------------------------------------------------

SQL> SELECT COUNT(*) FROM T WHERE DATA_OBJECT_ID IS NULL;

COUNT(*)
----------
46452

执行计划
----------------------------------------------------------
Plan hash value: 2587852253

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1| 2| 2 (0)|
| 1 | SORT AGGREGATE | | 1| 2| |
| 2 | BITMAP CONVERSION COUNT | | 46452| 92904| 2 (0)|
|* 3 | BITMAP INDEX SINGLE VALUE| IND_B_T_DATA_ID| | | |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("DATA_OBJECT_ID" IS NULL)

从上面的结果不难看出BITMAP索引中是包含NULL的。

下面看看复合索引的情况：

SQL> DROP INDEX IND_B_T_DATA_ID;

索引已删除。

SQL> CREATE INDEX IND_T_OBJECT_DATA ON T(OBJECT_ID, DATA_OBJECT_ID);

索引已创建。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', METHOD_OPT => 'FOR ALL INDEXED COLUMNS')

PL/SQL 过程已成功完成。

SQL> SELECT OBJECT_ID, DATA_OBJECT_ID FROM T WHERE OBJECT_ID = 135;

OBJECT_ID DATA_OBJECT_ID
---------- --------------
135

执行计划
----------------------------------------------------------
Plan hash value: 1726226519

---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 7 | 1 (0)|
|* 1 | INDEX RANGE SCAN| IND_T_OBJECT_DATA | 1 | 7 | 1 (0)|
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("OBJECT_ID"=135)

虽然结果中包含了NULL值，但是Oracle并没有读取表，而仅仅通过索引扫描就返回了结果，这说明复合索引中是可能包含NULL值的。

本文简单说明了索引和NULL值的关系。这里并没有对反键索引（reverse）、逆序索引（desc）、函数索引（FBI）和CLUSTER索引进行说明。

原因是这些索引其实都属于离不开BTREE索引和BITMAP索引的范畴。不必关心索引是否倒序或反键，只要是BTREE索引，就不会存储全NULL记录，反之，只要是BITMAP索引就会存储NULL值。

唯一需要注意的是函数索引，函数索引的真正索引列是函数的计算结果而不是行记录中的数据，清楚了这一点函数索引其实和普通索引就没有什么区别了。

最后说明一下域索引。由于域索引的实现本身可能会很复杂，Oracle可能在内部是用一套表和过程来实现的，因此对于域索引是否存储NULL，要根据域索引的实现去进行具体的分析了。

本文关注点仍然是在NULL上面，这篇文章打算通过实例来说明这些文章中和NULL相关的一些观点已经不适用于CBO优化器了。

观点一：判断一个列IS NOT NULL不会使用索引。

其实这个观点从一般意义上也解释不同，因为B树索引本身不存储键值全为NULL的记录，所以通过索引扫描得到的结果一定满足IS NOT NULL的要求。

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS;

表已创建。

SQL> CREATE INDEX IND_T_DATAID ON T(DATA_OBJECT_ID);

索引已创建。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL 过程已成功完成。

SQL> SET AUTOT TRACE
SQL> SELECT COUNT(*) FROM T WHERE DATA_OBJECT_ID IS NOT NULL;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=2)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'IND_T_DATAID' (NON-UNIQUE) (Cost=26 Card=2946 Bytes=5892)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
4 physical reads
0 redo size
377 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

由于索引的存储特性和IS NOT NULL访问本身没有冲突，因此，这种情况下很容易通过索引来得到相应的结果。

观点二：判断一个列IS NULL不会使用索引。

这里不讨论BITMAP索引。由于BITMAP索引保存NULL值，所以讨论BITMAP索引没有意义。这里仅讨论B树索引。

在上一篇文章里面其实已经讨论过了，B树索引不存储键值全为空的记录。所以对于单列索引而言，确实IS NULL操作是无法使用索引的。但是复合索引是可能存储一部分NULL值的，所以，IS NULL操作也并非不可能使用索引。

SQL> ALTER TABLE T MODIFY OWNER NOT NULL;

表已更改。

SQL> UPDATE T SET OBJECT_ID = NULL WHERE ROWNUM = 1;

已更新 1 行。

SQL> CREATE INDEX IND_T_OBJECT_OWNER ON T (OBJECT_ID, OWNER);

索引已创建。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T', METHOD_OPT => 'FOR ALL INDEXED COLUMNS SIZE 200')

PL/SQL 过程已成功完成。

SQL> SET AUTOT TRACE
SQL> SELECT * FROM T WHERE OBJECT_ID IS NULL;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=93)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=3 Card=1 Bytes=93)
2 1 INDEX (RANGE SCAN) OF 'IND_T_OBJECT_OWNER' (NON-UNIQUE) (Cost=2 Card=1)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
1156 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

从上面的两个例子可以看到，Oracle的CBO并不会因为SQL语句中指定了IS NOT NULL或IS NULL操作就不再使用索引。CBO选择索引的条件只有正确性和代价，只要满足这两个条件就可以了。