oracle 查询空值异常,Oracle中的NULL

这篇文档讨论一下NULL和索引的关系。

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

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

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

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

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

[PHP]

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 |

-------------------------------------------------------------------

.

[/PHP]

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

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

[PHP]

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)

.

[/PHP]

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

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

[PHP]

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)

.

[/PHP]

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

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

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

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

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

原文出自：http://space.itpub.net/4227/viewspace-69139

[本帖最后由 yangtingkun 于 2008-1-25 10:35 编辑]