头疼的null值，自敬彬

11、NULL的研究
在我看来,NULL是ORACLE中最"阴险"的东东,给开发人员带来了许多的麻烦！怎么个阴险狡诈呢？
下面例子都来自我在工作中发现并总结的小小心得，现在以实验的形式整理如下，希望对大家有用。

11.1、update丢失数据
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
SQL> drop table ljb_test1;
Table dropped
SQL> drop table ljb_test2;
Table dropped
SQL> create table ljb_test1 (id1 int,id2 int);
Table created
SQL> create table ljb_test2 (id1 int,id2 int);
Table created
SQL> insert into ljb_test1 (id1,id2)  values( 1,17);
1 row inserted
SQL> insert into ljb_test1 (id1,id2)  values( 2,18);
1 row inserted
SQL> insert into ljb_test1 (id1,id2)  values( null,18);
1 row inserted
SQL> insert into ljb_test2 (id1,id2)  values( 1,27);
1 row inserted
SQL> insert into ljb_test2 (id1,id2)  values( 2,28);
1 row inserted
SQL> insert into ljb_test2 (id1,id2)  values( null,29);
1 row inserted
SQL> commit;
Commit complete

查询两表记录情况情况，各自都有三条记录，但是分别都有空值存在
SQL> select * from ljb_test1;
ID1                                  ID2
--------------------------------------- ----------------------------------
1                                    17
2                                    18
                                          18
SQL> select * from ljb_test2;
ID1                                   ID2
--------------------------------------- --------------------------------
1                                     27
2                                     28
                                         29
接下来利用ljb_test2去更新ljb_test1，关联字段为id1，将ljb_test2表的id2的值去更新ljb_test1表的id2的值。
SQL> update ljb_test1 set id2=(select id2 from ljb_test2 where ljb_test1.id1=ljb_test2.id1);
3 rows updated
更新完毕后发现，ljb_test1表记录确实被更新了，不过却丢失了id1为空的那条记录
SQL> select  * from ljb_test1;
ID1                                    ID2
--------------------------------------- -------------------------------
1                                      27
2                                      28
SQL> select  * from ljb_test2;
ID1                                   ID2
--------------------------------------- -------------------------------
1                                      27
2                                      28
                                          29
上述脚本未考虑空值，闯了大祸，平空丢了数据，回退数据
SQL> rollback;
Rollback complete
  更改脚本，添加了如下代码where exists (select 1 from ljb_test2 where ljb_test1.id1=ljb_test2.id1)，确保更新的结果集是ljb_test2的id1列在ljb_test1中的id1列中都能找到的结果集。避免了空值问题！
SQL> update ljb_test1 set id2=(select id2 from ljb_test2 where ljb_test1.id1=ljb_test2.id1)
     where exists (select 1 from ljb_test2 where ljb_test1.id1=ljb_test2.id1);
2 rows updated
接着查看，发现ljb_test1记录被正确更新了，这下空值记没丢失了，仍然是三条记录。
SQL> select  * from ljb_test1;
ID1                                    ID2
--------------------------------------- --------------------------------------
1                                         27
2                                         28
                                              18
SQL> select  * from ljb_test2;
ID1                                     ID2
--------------------------------------- ---------------------------------------
1                                       27
2                                       28
                                           29
     总结：NULL空值会造成表更新过程中数据的丢失，要千万小心操作！



11.2 update 更新错数据

继续做实验如下，还是null值，不过这回null值出现在id2列了。
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
查看表记录情况如下：
SQL> select * from ljb_test1;
ID1                                     ID2
------------------------------------------------------------------
                                          3
1                                       17
2                                       18
3                                       16
SQL>  select * from ljb_test2;
ID1                                    ID2
-------------------------------------------------------------------
  5
  1                                       27
2                                       28
仍用欠考虑空值的脚本进行更新：
SQL> update ljb_test1 set id2=(select id2 from ljb_test2 where ljb_test1.id1=ljb_test2.id1);
4 rows updated
查看结果，发现更新没有导致丢失了数据，却是更新出问题了，把ljb_test1表原来的id1=3，id2=16的记录更新为id2为null了，这个显然不是要的结果！为什么会这样呢？
由于在ljb_test2中不存在id1字段值为3的记录.这样在UPDATE的时候,ljb_test2表中找不到就会用null去UPDATE t1表id1=3对应的id2列的值，于是值由16更新为null

SQL> select * from ljb_test1;
ID1                                     ID2
-------------------------------------------------------------------
3
1                                      27
2                                      28
                                        3
SQL>  select * from ljb_test2;
ID1                                   ID2
--------------------------------------------------------------------
5
1                                      27
               2                                      28
回退程序
SQL> rollback;
Rollback complete
   用改进后的脚本执行如下
SQL> update ljb_test1 set id2=(select id2 from ljb_test2 where ljb_test1.id1=ljb_test2.id1)
     where exists (select 1 from ljb_test2 where ljb_test1.id1=ljb_test2.id1);
2 rows updated
    现在才是正确的结果，没有出现更新错误的情况了!
SQL>  select * from ljb_test1;
ID1                                  ID2
--------------------------------------- --------------------------
3
1                                      27
2                                      28
3                                      16
SQL>  select * from ljb_test2;
ID1                                  ID2
--------------------------------------- ----------------------------
5
1                                      27
2                                      28

总结：NULL空值会造成表更新过程中数据的更新错误，要千万小心操作！


11.3、ORACLE的NULL既等又不等

Null值=Null值吗？有的人回答说Null=Null，有的人说Null<>Null，到底是等还是不等？先做实验看看
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
SQL> select * from dual ;
DUMMY
-----
X
SQL> select * from dual where 1=1;
DUMMY
-----
X
SQL> select * from dual where 1=2;
DUMMY
-----
通过上述操作知道，可以用dual来证明一下Null到底等于不等于Null
先看看是否相等？
SQL> select * from dual where null=null;
DUMMY
-----
看来是不等，回答不等的朋友高兴了，继续实验如下
SQL> select * from dual where null<>null;
DUMMY
-----
这下大家都有点摸不着头脑了，怎么条件也是假？也就是说在ORACLE中null=null或者null<>null都不是对的！
再看下面的写法
SQL> select * from dual where null is null;
DUMMY
-----
X
看来也不是不完全不等的！
总结：这里这样理解才是正确的：“ORACLE把NULL值看成了一个未知的东西！”这点很特殊，在SQL Server和Sybase等其他版本的数据库中不是这样的，他们认为NULL=NULL是正确的，因此要特别留意ORACLE在NULL上的特殊性，再回到前一小节中丢失数据看看，应该更加深刻的明白了为什么会丢失的原因，如果是SQL Server，前面的更新是不会丢失数据的，只是空值列对应的id2列会被test2表的id1为空的id2的值更新！
那我们在写程序中要注意啥呢？对了NVL(VALUE1,0)=NVL(VALUE2,0) 这个是一个不错的应对方法！


11.4 索引与NULL
大家对count（*）是否用得到索引的常识还是有点印象吧。 select  count(*) from test 原来用不到索引(object_id列有一个普通索引），我加了“where object_id is not null”后便用走索引了，换个思路，对object_id改建主键后，索引也可以用到了，啥原因？
究其根本，就是因为索引不能存储NULL！
本小节我将简要的说明索引和NULL的关系
SQL> drop table ljb_test;
Table dropped
SQL> create table ljb_test (x int,y int);
Table created
SQL> create unique index idx_ljb_test on ljb_test(x,y);
Index created
SQL> insert into ljb_test values(1,1);
1 row inserted
SQL> insert into ljb_test values(1,null);
1 row inserted
SQL> insert into ljb_test values(null,1);
1 row inserted
SQL> insert into ljb_test values(null,null);
1 row inserted
下面执行前面我多次使用过的analyze index idx_ljb_test validate structure;命令
SQL> analyze index idx_ljb_test validate structure;
Index analyzed
查看索引存储的行数，很清楚的看明白了，当前索引只存储了三行，而实际有四行，未存储的就是null的那行记录
SQL> select name,lf_rows from  index_stats;
NAME                              LF_ROWS
------------------------------ --------------------------------------------
IDX_LJB_TEST                            3
SQL> insert into ljb_Test values(1,null);
insert into ljb_Test values(1,null)
ORA-00001: 违反唯一约束条件 (LJB.IDX_LJB_TEST)
看来建了唯一索引后前面的数据不允许插入很正常，那试验一下插入null,null是什么情况？
SQL> insert into ljb_Test values(null,null);
1 row inserted
最终的结果是发现插入成功了！有就是说ORACLE并不认为这个null,null值是重复插入过的,也就是说情况如我说的4.7.2小节的情况，oracle把索引当成了一种未知，也就是null既不等于null也不完全不等于null
    最后执行一个统计语句，这下发现奇怪了，怎么在统计分类中，oracle又把null归为了一类，原来在oracle中考虑唯一性的时候（null,null）与(null,null不同，而聚合查询语句又认为是一样的了！看来这个null要好好体会一下了。
SQL> select x,y,count(*) from ljb_test group by x,y having count(*)>1;
   X              Y                  COUNT(*)
--------------------------------------- ---------------------------------------
                                           2
总结：ORACLE的索引不能存储空值，这点大家通过实验应该有了比较深刻的体会！另外再问大家一点，select * from test_ljb where object_id is null ,假如object_id有索引，这个索引能用到吗？一起研究了这么多，这个问题应该变的相当容易回答吧！
NULL阴险归阴险，用清楚，确认是否真的需要非空，还可以让COUNT(*)提速，呵呵。

11.5、反连接与NULL

SQL> connect ljb/ljb;
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as ljb
SQL> alter system flush buffer_cache;
System altered
SQL> explain plan for select * from dept where deptno NOT IN ( select deptno from emp ) ;
Explained

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 3547749009
-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    57 |     6   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    76 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     3 |    39 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "EMP" "EMP" WHERE
              LNNVL("DEPTNO"<>:B1)))
   3 - filter(LNNVL("DEPTNO"<>:B1))
17 rows selected


SQL> explain plan for select * from dept where deptno NOT IN ( select deptno from emp where deptno is not null) and deptno is not null;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 474461924
-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    96 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     3 |    96 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    76 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     1 |    13 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPTNO"="DEPTNO")
   3 - filter("DEPTNO" IS NOT NULL)
16 rows selected

这里看到ANTI的熟悉的关键字了吧，这个表示反连接的意思，其实反连接的算法也是经过ORACLE优化过的一种内部算法，但是当关键谓词列如果不是非空，将无法使用到该算法。
用到后咋样呢？COST由6变5，改进了，没打出STATISTICS，事实上逻辑读也大大减小了。看来NULL阴险归阴险，用好也是有好处的。

试验中的两处错误：

楼主写的不错，可是有两处明显的错误哦。
第一处，第一个实验（11.1）：
--------------------------------------------------------------------------------------------------------------------------
接下来利用ljb_test2去更新ljb_test1，关联字段为id1，将ljb_test2表的id2的值去更新ljb_test1表的id2的值。
SQL> update ljb_test1 set id2=(select id2 from ljb_test2 where ljb_test1.id1=ljb_test2.id1);
3 rows updated
更新完毕后发现，ljb_test1表记录确实被更新了，不过却丢失了id1为空的那条记录
SQL> select  * from ljb_test1;
ID1                                    ID2
--------------------------------------- -------------------------------
1                                      27
2                                      28
--------------------------------------------------------------------------------------------------------------------------

其实返回的结果有三条记录，第三条记录是ID1 NULL ,ID2 NULL.


第二处，11.5实验：
--------------------------------------------------------------------------------------------------------------------------
SQL> explain plan for select * from dept where deptno NOT IN ( select deptno from emp where deptno is not null) and deptno is not null;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------
Plan hash value: 474461924
-------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    96 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     3 |    96 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| DEPT |     4 |    76 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| EMP  |     1 |    13 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("DEPTNO"="DEPTNO")
   3 - filter("DEPTNO" IS NOT NULL)
16 rows selected
--------------------------------------------------------------------------------------------------------------------------

不仅not in后面的查询需要加条件deptno is not null，前面的查询也需要加这个条件deptno is not null。要不还会是过滤操作。而不会是hash join anti操作。

SQL> explain plan for
  2   select * from ljb_test1 where id1 not in(select id1 from ljb_test2 where id1 is not null);
已解释。
已用时间:  00: 00: 00.05
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
--------
Plan hash value: 384128341
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    26 |     6   (0)| 00:00:01 |
|*  1 |  FILTER            |           |       |       |            |          |
|   2 |   TABLE ACCESS FULL| LJB_TEST1 |     3 |    78 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| LJB_TEST2 |     2 |    26 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "LJB_TEST2" "LJB_TEST2"
              WHERE "ID1" IS NOT NULL AND LNNVL("ID1"<>:B1)))
   3 - filter("ID1" IS NOT NULL AND LNNVL("ID1"<>:B1))

SQL> explain plan for
  2  select * from ljb_test1 where id1 is not null and id1 not in(select id1 from ljb_test2 where id1 is not null);
已解释。
已用时间:  00: 00: 00.05
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 2688661253
--------------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    39 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |           |     1 |    39 |     7  (15)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| LJB_TEST1 |     2 |    52 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| LJB_TEST2 |     2 |    26 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ID1"="ID1")
   2 - filter("ID1" IS NOT NULL)
   3 - filter("ID1" IS NOT NULL)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-667721/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-667721/