Oracle中NULL的主意事项和用法.doc

最近在论坛上经常看到，很多人提出和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的=、!=、=、 CREATE OR REPLACE PROCEDURE P1 (P_IN IN NUMBER) AS2 BEGIN3 IF P_IN = 0 THEN 4 DBMS_OUTPUT.PUT_LINE(TRUE);5 ELSE6 DBMS_OUTPUT.PUT_LINE(FALSE);7 END IF;8 END;9 /过程已创建。SQL CREATE OR REPLACE PROCEDURE P2 (P_IN IN NUMBER) AS2 BEGIN3 IF P_IN SET SERVEROUT ONSQL EXEC P1(NULL)FALSEPL/SQL 过程已成功完成。SQL EXEC P2(NULL)TRUEPL/SQL 过程已成功完成。输入为NULL时，上面两个过程中的判断的结果都是一样的，不管是NULL = 0还是NULL SET SERVEROUT ON SIZE 100000SQL DECLARE2 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) AS7 V_RESULT BOOLEAN;8 BEGIN9 IF P_IN1 IS NULL THEN10 DBMS_OUTPUT.PUT(NULL );11 ELSIF P_IN1 THEN12 DBMS_OUTPUT.PUT(TRUE );13 ELSE14 DBMS_OUTPUT.PUT(FALSE );15 END IF;16 17 IF P_OPERATOR = AND THEN18 DBMS_OUTPUT.PUT(AND );19 V_RESULT := P_IN1 AND P_IN2;20 ELSIF P_OPERATOR = OR THEN21 DBMS_OUTPUT.PUT(OR );22 V_RESULT := P_IN1 OR P_IN2;23 ELSE24 RAISE_APPLICATION_ERROR(-20000, INPUT PARAMETER P_OPERATOR ERROR);25 END IF;26 27 IF P_IN2 IS NULL THEN28 DBMS_OUTPUT.PUT(NULL);29 ELSIF P_IN2 THEN30 DBMS_OUTPUT.PUT(TRUE);31 ELSE32 DBMS_OUTPUT.PUT(FALSE);33 END IF;34373941 END IF;42 DBMS_OUTPUT.NEW_LINE;43 END;44 45 BEGIN46 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 LOOP51 FOR J IN 1.V_BOOL2.COUNT LOOP52 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:TRUETRUE OR TRUE:TRUETRUE AND FALSE:FALSETRUE OR FALSE:TRUETRUE AND NULL:NULLTRUE OR NULL:TRUEFALSE AND TRUE:FALSEFALSE OR TRUE:TRUEFALSE AND FALSE:FALSEFALSE OR FALSE:FALSEFALSE AND NULL:FALSEFALSE OR NULL:NULLNULL AND TRUE:NULLNULL OR TRUE:TRUENULL AND FALSE:FALSENULL OR FALSE:NULLNULL AND NULL:NULLNULL OR NULL:NULLPL/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操作图表变为：ANDTRUEFALSENULLTRUETRUEFALSENULLFALSEFALSEFALSEFALSENULLNULLFALSENULLOR操作图表变为：ORTRUEFALSENULLTRUETRUETRUETRUEFALSETRUEFALSENULLNULLTRUENULLNULL最后，仍然来看一个例子：SQL SELECT * FROM TAB;TNAME TABTYPE CLUSTERID- - -PLAN_TABLE TABLET TABLET1 TABLET2 TABLET3 TABLETEST TABLETEST1 TABLETEST_CORRUPT TABLET_TIME TABLE已选择9行。SQL SELECT * FROM TAB WHERE TNAME IN (T, T1, NULL);TNAME TABTYPE CLUSTERID- - -T TABLET1 TABLESQL 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 100000SQL DECLARE2 TYPE T_BOOLEAN IS TABLE OF BOOLEAN INDEX BY BINARY_INTEGER;3 V_BOOL T_BOOLEAN;4 5 PROCEDURE P(P_IN BOOLEAN) AS6 V_RESULT BOOLEAN;7 BEGIN8 IF P_IN IS NULL THEN9 DBMS_OUTPUT.PUT(NOT NULL);10 ELSIF P_IN THEN11 DBMS_OUTPUT.PUT(NOT TRUE);12 ELSE13 DBMS_OUTPUT.PUT(NOT FALSE);14 END IF;15 16 V_RESULT := NOT P_IN;17202224 END IF;25 DBMS_OUTPUT.NEW_LINE;26 END;27 28 BEGIN29 V_BOOL(1) := TRUE;30 V_BOOL(2) := FALSE;31 V_BOOL(3) := NULL;32 FOR I IN 1.V_BOOL.COUNT LOOP33 P(V_BOOL(I);34 END LOOP; 35 END;36 /NOT TRUE:FALSENOT FALSE:TRUENOT NULL:NULLPL/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 TRUENOT FALSENOT NULLFALSETRUENULL以前我总说空字符串等价于NULL，但是有些人喜欢钻牛角尖，所以我改一下说法，空字符串是NULL的字符类型的表现格式。也许有人会认为，NULL就是NULL，本身没有类型的一说，但是我认为，NULL还是有类型的，只不过不同类型的NULL都用相同的关键字NULL来表示。而且，NULL本身也可以转化为任意类型的数据，因此给人的感觉是NULL没有数据类型。其实NULL不但有数据类型，还有默认的数据类型，那就是字符类型。至于这个答案是如何推断出来的，请看：/post/468/50132不过上面说的这个默认的数据类型是在极限的情况下测试出来的，如果只是给出一个NULL，那么它是可以代表任意的类型的。证明空字符串就是NULL是很容易的：SQL SELECT 1 FROM DUAL WHERE = ;未选定行SQL SELECT 1 FROM DUAL WHERE IS NULL;1-1SQL 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 AS2 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 AS4 BEGIN5 RETURN NUMBER;6 END;7 8 FUNCTION F_RETURN (P_IN IN VARCHAR2) RETURN VARCHAR2 AS9 BEGIN10 RETURN VARCHAR2;11 END;12 13 END;14 /程序包体已创建。SQL SELECT P_TEST_NULL.F_RETURN(3) FROM DUAL;P_TEST_NULL.F_RETURN(3)-NUMBERSQL SELECT P_TEST_NULL.F_RETURN(3) FROM DUAL;P_TEST_NULL.F_RETURN(3)-VARCHAR2SQL SELECT P_TEST_NULL.F_RETURN() FROM DUAL;P_TEST_NULL.F_RETURN()-VARCHAR2SQL 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-XSQL 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 NUMBERDATA_OBJECT_ID NUMBEROBJECT_TYPE VARCHAR2(19)CREATED DATELAST_DDL_TIME DATETIMESTAMP 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 EXPSQL 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 TRACESQL 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 calls0 db block gets5 consistent gets4 physical reads0 redo size377 bytes sent via SQL*Net to client503 bytes received via