Oracle中NULL的注意事项和用法

    技术2025-02-17  16

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

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

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

     

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

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

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

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

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

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

    说了怎么多,来看一个经典的例子:

    SQL> 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 < 0 THEN 4 DBMS_OUTPUT.PUT_LINE('FALSE');5 ELSE6 DBMS_OUTPUT.PUT_LINE('TRUE');7 END IF;8 END;9 /

    过程已创建。

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

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

    SQL> SET SERVEROUT ONSQL> 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的情况,这使得布尔值从原来的TRUEFALSE两个值变成了TRUEFALSENULL三个值。

    下面是TRUEFALSE两种情况进行布尔运算的结果:

    AND操作:

    AND

    TRUE

    FALSE

    TRUE

    TRUE

    FALSE

    FALSE

    FALSE

    FALSE

    OR操作:

    OR

    TRUE

    FALSE

    TRUE

    TRUE

    TRUE

    FALSE

    TRUE

    FALSE

    上面是熟悉的TRUEFALSE两个值进行布尔运算的结果,如果加上一个NULL的情况会怎样?NULL的布尔运算是否会像NULL的算术运算那样结果都是NULL呢?下面通过一个过程来进行说明:

    SQL> 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:NULL

    PL/SQL 过程已成功完成。

    由于NULL是未知,所以NULL AND NULLNULL OR NULLNULL AND TRUENULL OR FALSE的值都是未知的,这些的结果仍然是NULL

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

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

    同样的道理,TRUE AND TRUEFALSE AND TRUE的结果都是TRUE,所以不管NULL取何值,NULLTRUEOR的结果都是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 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 TABLE

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

    未选定行

    对于INNOT INNULL的关系前面并没有说明,不过可以对其进行简单的变形:

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

    根据前面的结果,当查询到TT1这两条记录时,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,所以,查询不会返回记录。

    对于TRUEFALSENOT运算很简单,NOT TRUE=FALSENOT 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:NULL

    PL/SQL 过程已成功完成。

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

    NULL表示的是未知的含义,而增加一个NOT操作后,并不能使NULL变为一个确定的值,如果是TRUENOT TRUE将变为FALSE,如果是FALSENOT 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 NULLOracle的语法,在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)------------------------------------------------------------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语言中的空字符串””对应OracleASCII表中的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

    首先索引分为BTREEBITMAP两种,对于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 SQL*Net from client2 SQL*Net roundtrips to/from client0 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 calls0 db block gets3 consistent gets0 physical reads0 redo size1156 bytes sent via SQL*Net to client503 bytes received via SQL*Net from client2 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)1 rows processed

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

     

    最新回复(0)