Oracle中的NULL

技术2022-05-20 50

ORACLE中的NULL，这篇帖子总结的非常好。http://www.itpub.net/thread-932786-1-1.html转载：

(1)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 ONSQL> EXEC P1(NULL)FALSEPL/SQL 过程已成功完成。SQL> EXEC P2(NULL)TRUEPL/SQL 过程已成功完成。

--------------------------------------------------------------------------------输入为NULL时，上面两个过程中的判断的结果都是一样的，不管是NULL >= 0还是NULL < 0结果都是未知，所以两个判断的结果都是NULL。最终，在屏幕上输出的都是ELSE后面跟的输出值。由于NULL所具有的特殊性，在处理数据库相关问题时应该对NULL的情况额外考虑，否则很容易造成错误。

(2)NULL布尔运算特点由于引入了NULL，在处理逻辑过程中一定要考虑NULL的情况。同样的，数据库中的布尔值的处理，也是需要考虑NULL的情况，这使得布尔值从原来的TRUE、FALSE两个值变成了TRUE、FALSE和NULL三个值。下面是TRUE和FALSE两种情况进行布尔运算的结果：AND操作：AND TRUE FALSETRUE TRUE FALSEFALSE FALSE FALSE

OR操作：OR TRUE FALSETRUE TRUE TRUEFALSE TRUE FALSE

上面是熟悉的TRUE和FALSE两个值进行布尔运算的结果，如果加上一个NULL的情况会怎样？NULL的布尔运算是否会像NULL的算术运算那样结果都是NULL呢？下面通过一个过程来进行说明：--------------------------------------------------------------------------------SQL> SET SERVEROUT ON SIZE 100000SQL> 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 35 IF V_RESULT IS NULL THEN 36 DBMS_OUTPUT.PUT(':NULL'); 37 ELSIF V_RESULT THEN 38 DBMS_OUTPUT.PUT(':TRUE'); 39 ELSE 40 DBMS_OUTPUT.PUT(':FALSE'); 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: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:NULL

由于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。

最后，仍然来看一个例子：--------------------------------------------------------------------------------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> 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 18 IF V_RESULT IS NULL THEN 19 DBMS_OUTPUT.PUT(':NULL'); 20 ELSIF V_RESULT THEN 21 DBMS_OUTPUT.PUT(':TRUE'); 22 ELSE 23 DBMS_OUTPUT.PUT(':FALSE'); 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: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。

(3)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---------- 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 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)------------------------------------------------------------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。--------------------------------------------------------------------------------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里面的空字符串并非不不含任何数据，里面还包含了一个字符串结束符/0。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;未选定行--------------------------------------------------------------------------------

(4)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 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，要根据域索引的实现去进行具体的分析了。

(5)NULL对SQL使用索引的影响最近在论坛上看到很多人从网上找到一些SQL优化的注意点，其中有不少就和NULL的处理有关。遗憾的是，找到的优化文章都是比较老的，一般只适用于RBO下，而对CBO以及不再适用了。本文关注点仍然是在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 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 TRACESQL> 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选择索引的条件只有正确性和代价，只要满足这两个条件就可以了。

(6)SQL和PLSQL中处理NULL的一些问题

NULL的最大的特点就是两个NULL是不相等的。如果用等号来判断两个NULL是否相等得到的结果一定是NULL。从唯一约束的特点也可以看到，对于建立了唯一约束的列，Oracle允许插入多个NULL值，这时因为Oracle不认为这些NULL是相等的。--------------------------------------------------------------------------------SQL> CREATE TABLE T (ID NUMBER, CONSTRAINT UN_T UNIQUE(ID));表已创建。SQL> INSERT INTO T VALUES (1);已创建 1 行。SQL> INSERT INTO T VALUES (1);INSERT INTO T VALUES (1)*ERROR 位于第 1 行:ORA-00001: 违反唯一约束条件 (YANGTK.UN_T)

SQL> INSERT INTO T VALUES (NULL);已创建 1 行。SQL> INSERT INTO T VALUES (NULL);已创建 1 行。--------------------------------------------------------------------------------

但是有的时候，Oracle会认为NULL是相同的，比如在GROUP BY和DISTINCT操作中。这个时候，Oracle会认为所有的NULL都是一类的。还有一种情况，就是在DECODE函数中。如果表达式为DECODE(COL, NULL, 0, 1)，那么如果COL的值为NULL，Oracle会认为这种情况与第二个参数的NULL值相匹配，会返回0。不过这里只是给人感觉NULL值是相等的，Oracle在实现DECODE函数的时候，仍然是通过IS NULL的方式进行的判断。对于大多数的常用函数来说，如果输入为NULL，则输出也是NULL。NVL、NVL2、DECODE和||操作是个例外。他们在输入参数为NULL的时候，结果可能不是NULL。不过归结其原因是因为，这些函数都有多个参数，当多个参数不全为NULL时，结果可能不是NULL，如果输入参数均为NULL，那么得到的输出结果也是NULL。NULL还有一个特点，就是一般聚集函数不会处理NULL值。不管是MAX、MIN、AVG还是SUM，这些聚集函数都不会处理NULL。注意这里说的不会处理NULL，是指聚集函数会直接忽略NULL值记录的存在。除非是聚集函数处理的列中包含的全部记录都是NULL，这种情况下，上面这些聚集函数会返回NULL值。

--------------------------------------------------------------------------------SQL> DELETE T WHERE ID = 1;已删除 1 行。SQL> SELECT NVL(TO_CHAR(ID), 'NULL') FROM T;NVL(TO_CHAR(ID),'NULL')----------------------------------------NULLNULLSQL> SELECT MAX(ID) FROM T; MAX(ID)----------

SQL> SELECT AVG(ID) FROM T; AVG(ID)----------

SQL> INSERT INTO T VALUES (1);已创建 1 行。--------------------------------------------------------------------------------

聚集函数中比较特殊的是COUNT，第一个特殊点是COUNT不会返回NULL值，即使表中没有记录，或者COUNT(COL)中，COL列的记录全为NULL，COUNT也会返回0值而不是NULL。第二个特殊点就是COUNT(*)或COUNT(常量)的形式。这种形式使得COUNT可以计算包含NULL记录在内的记录总数。

--------------------------------------------------------------------------------SQL> SELECT COUNT(*), COUNT(1), COUNT('A'), COUNT(ID), COUNT(NULL) FROM T; COUNT(*) COUNT(1) COUNT('A') COUNT(ID) COUNT(NULL)---------- ---------- ---------- ---------- ----------- 3 3 3 1 0

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

最后简单说一下AVG，AVG(COL)等价于SUM(COL)/COUNT(COL)，不等价于SUM(COL)/COUNT(*)：--------------------------------------------------------------------------------SQL> SELECT AVG(ID), SUM(ID)/COUNT(ID), SUM(ID)/COUNT(*) FROM T; AVG(ID) SUM(ID)/COUNT(ID) SUM(ID)/COUNT(*)---------- ----------------- ---------------- 1 1 .333333333

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

(7)NULL在Oracle中的默认数据类型是什么Oracle的NULL代表的含义是不确定，那么不确定的东西也会有确定的数据类型吗？或者换个说法，NULL在Oracle中的默认数据类型是什么，下面就来探讨这个问题。

首先公布答案，NULL的默认类型是字符类型，具体是VARCHAR2还是CHAR，这个并不清楚，不过我个人怀疑是VARCHAR2的可能性更大一些。我们知道一个字段不管是何种类型的，都可以插入NULL值，也就是说，NULL可以随意的转换为任意的类型。而且，绝大部分的函数输入值为NULL，返回的结果也为NULL，这就阻止了我们通过函数的返回结果判断NULL的类型的企图。我们最常用来分析数据的DUMP函数，这回也实效了：--------------------------------------------------------------------------------SQL> SELECT DUMP(NULL) FROM DUAL;DUMP----NULL而且试图通过CREATE TABLE AS来判定NULL的类型也是不可能的：SQL> CREATE TABLE T AS SELECT TNAME, NULL COL1 FROM TAB;CREATE TABLE T AS SELECT TNAME, NULL COL1 FROM TAB *ERROR 位于第 1 行:ORA-01723: 不允许长度为 0 的列--------------------------------------------------------------------------------

可能有人会产生疑问，既然各种方法的行不通，你是怎么得到NULL的默认类型的？也许还有人会想，既然NULL可以隐式的转化为任意的类型，讨论NULL的默认类型是否有意义呢？下面就是我发现NULL的数据类型的例子，同时说明了如果不注意NULL的数据类型可能会出现的问题。由于原始的SQL过于复杂，我这里给出一个简化的例子。--------------------------------------------------------------------------------SQL> create table t (id number);表已创建。SQL> insert into t values (1);已创建 1 行。SQL> insert into t values (8);已创建 1 行。SQL> insert into t values (0);已创建 1 行。SQL> insert into t values (15);已创建 1 行。SQL> commit;提交完成。--------------------------------------------------------------------------------

需要按照T中的ID的升序显示数据，SQL如下：--------------------------------------------------------------------------------SQL> select * from t order by id; ID---------- 0 1 8 15

--------------------------------------------------------------------------------需求还有一点点小的要求，对于0值这个比较特殊的值，在所有非0值的后面显示。当然实现的方法比较多，比如使用UNION ALL将非0值和0值分开，或者将0值转换为一个很大的数值。由于ID的最大值不确定，且考虑使用一个简单的SQL完成，我选择了在排序的时候将0值转化为NULL的方法，这样利用排序时NULL最大的原理，得到我希望的结果。SQL如下：--------------------------------------------------------------------------------SQL> select * from t order by decode(id, 0, null, id); ID---------- 1 15 8 0

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

0值确实如我所愿排在了最后，但是结果怎么“不对”了！

--------------------------------------------------------------------------------SQL> select decode(id, 0, null, id) from t;DECODE(ID,0,NULL,ID)----------------------------------------1815

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

看看DECODE函数的结果，这回明白了，原来DECODE的结果变为了字符类型。字符类型结果在SQLPLUS显示左对齐，而数值类型是右对齐。在DECODE函数中，输入的4个参数中两个ID和0都是NUMBER类型，只有NULL这一个输入值类型不确定，莫非是由于NULL的类型是字符类型？猜测只是猜测，还需要确切的证据证明这一点，下面看看标准包中DECODE函数的定义。下面的DECODE函数定义是从STANDARD中摘取出来的部分内容：--------------------------------------------------------------------------------function DECODE (expr NUMBER, pat NUMBER, res NUMBER) return NUMBER;function DECODE (expr NUMBER, pat NUMBER, res VARCHAR2 CHARACTER SET ANY_CS) return VARCHAR2 CHARACTER SET res%CHARSET;function DECODE (expr NUMBER, pat NUMBER, res DATE) return DATE;function DECODE (expr VARCHAR2 CHARACTER SET ANY_CS, pat VARCHAR2 CHARACTER SET expr%CHARSET, res NUMBER) return NUMBER;function DECODE (expr VARCHAR2 CHARACTER SET ANY_CS, pat VARCHAR2 CHARACTER SET expr%CHARSET, res VARCHAR2 CHARACTER SET ANY_CS) return VARCHAR2 CHARACTER SET res%CHARSET;function DECODE (expr VARCHAR2 CHARACTER SET ANY_CS, pat VARCHAR2 CHARACTER SET expr%CHARSET, res DATE) return DATE;function DECODE (expr DATE, pat DATE, res NUMBER) return NUMBER;function DECODE (expr DATE, pat DATE, res VARCHAR2 CHARACTER SET ANY_CS) return VARCHAR2 CHARACTER SET res%CHARSET;function DECODE (expr DATE, pat DATE, res DATE) return DATE;--------------------------------------------------------------------------------

通过观察上面的定义，我们不难发现，虽然Oracle对DECODE函数进行了大量的重载，且DECODE函数支持各种的数据类型，但是DECODE函数具有一个规律，就是DECODE函数的返回值的类型和DECODE函数的输入参数中第一个用来返回的参数的数据类型一致。可能不太好理解，举个简单的例子：--------------------------------------------------------------------------------SQL> select decode(id, 1, '1', 2) from t;D-1222SQL> select decode(id, '1', 1, '2') from t;DECODE(ID,'1',1,'2')-------------------- 1 2 2 2

--------------------------------------------------------------------------------从这两个简单的例子就可以看出，DECODE的返回值的数据类型和DECODE函数中第一个表示返回的参数的数据类型一致。从这点就可以看出，NULL的默认数量类型是字符类型，这才导致DECODE的结果变成了字符串，而查询根据字符串的排序比较，因此’15’小于’8’。知道了问题的原因，解决的方法就很多了，比如：--------------------------------------------------------------------------------SQL> select * from t order by decode(id, 1, 1, 0, null, id); ID---------- 1 8 15 0SQL> select * from t order by to_number(decode(id, 0, null, id)); ID---------- 1 8 15 0SQL> select * from t order by decode(id, 0, cast(null as number), id); ID---------- 1 8 15 0SQL> select * from t order by decode(id, 0, to_number(null), id); ID---------- 1 8 15 0--------------------------------------------------------------------------------

(8)CHECK约束中的NULL条件

今天同事在添加约束的时候，意外的添加了一个NULL值约束，导致了约束的失效。

看一个简单的例子：--------------------------------------------------------------------------------SQL> CREATE TABLE T 2 ( 3 ID NUMBER, 4 FLAG CHAR(1) NOT NULL, 5 CONSTRAINT CK_FLAG CHECK (FLAG IN ('A')) 6 );表已创建。SQL> INSERT INTO T (ID, FLAG) VALUES (1, 'A');已创建 1 行。SQL> INSERT INTO T (ID, FLAG) VALUES (1, 'B');INSERT INTO T (ID, FLAG) VALUES (1, 'B')*第 1 行出现错误:ORA-02290: 违反检查约束条件 (YANGTK.CK_FLAG)

SQL> INSERT INTO T (ID, FLAG) VALUES (1, NULL);INSERT INTO T (ID, FLAG) VALUES (1, NULL) *第 1 行出现错误:ORA-01400: 无法将 NULL 插入 ("YANGTK"."T"."FLAG")--------------------------------------------------------------------------------

由于FLAG列上的CHECK约束和NOT NULL约束，会导致除了’A’以外的数据都无法插入。但是如果在约束上添加一个NULL值：PHP code:--------------------------------------------------------------------------------SQL> ALTER TABLE T DROP CONSTRAINT CK_FLAG;表已更改。SQL> ALTER TABLE T ADD CONSTRAINT CK_FLAG CHECK (FLAG IN ('A', ''));表已更改。--------------------------------------------------------------------------------

Oracle不会检查同一个列上的多个CHECK约束是否会发生冲突，因此上面添加FLAG IN NULL这个约束并不会报错。但是这种添加的NULL，会导致这个CHECK约束失效：PHP code:--------------------------------------------------------------------------------SQL> INSERT INTO T (ID, FLAG) VALUES (1, 'A');已创建 1 行。SQL> INSERT INTO T (ID, FLAG) VALUES (1, 'B');已创建 1 行。SQL> INSERT INTO T (ID, FLAG) VALUES (1, 'C');已创建 1 行。SQL> INSERT INTO T (ID, FLAG) VALUES (1, NULL);INSERT INTO T (ID, FLAG) VALUES (1, NULL) *第 1 行出现错误:ORA-01400: 无法将 NULL 插入 ("YANGTK"."T"."FLAG")--------------------------------------------------------------------------------

现在CHECK约束CK_FLAG已经完全失去作用，对于FLAG列除了NULL值外，其他任何值都可以插入。CHECK的约束保证插入的数据不会导致CHECK检查的结果为假，CHECK结果为真和为空的记录都可以插入。对于FLAG IN (‘A’, ‘’)的约束而言，任何记录的CHECK结果不是TRUE就是NULL。因此任何记录都是可以插入的。

=========================================================================

作为对比，SQLServer中的NULL，可以参考

http://msdn.microsoft.com/en-us/library/aa196339(SQL.80).aspx

SQL Server中有一个ANSI_NULLS选项，设为ON或OFF时，判断一个字段是否为NULL的结果如下表所示：ANSI_NULLS Search Condition Result Value-------------------------------------------------ON ColumnA = NULL UNKNOWNColumnA IS NULL TRUE or FALSE--------------------------------------------OFF ColumnA = NULL TRUE or FALSEColumnA IS NULL TRUE or FALSE-------------------------------------------------

专利

最新回复(0)