頭疼的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陰險歸陰險，用好也是有好處的。
試驗中的兩處錯誤：
在我看來,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)
第一處，第一個實驗（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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 最令人頭疼的Python問題Python
- 【NULL】Oracle null值介紹NullOracle
- hive中的null值HiveNull
- JavaSE基礎:”頭疼”的正規表示式Java
- 教程中 令人頭疼的 前端流安裝前端
- JavaSE基礎:"頭疼"的正規表示式Java
- 程式設計師最頭疼的事：命名程式設計師
- 模組的封裝（四）：標頭檔案的疼封裝
- SQL中的空值NULLSQLNull
- SQL server中的NULL值SQLServerNull
- Bean類自動生成判斷null值的Set()和Get()方法BeanNull
- 關於 Java 8 的6大頭疼問題Java
- 程式設計的時候最頭疼的事：命名程式設計
- 面試題((A)null).fun()——java中null值的強轉面試題NullJava
- NULL 值與索引Null索引
- 不再迷惑，無值和 NULL 值Null
- 那些年讓我們頭疼的CSS3動畫CSSS3動畫
- MySQL null值儲存，null效能影響MySqlNull
- SQL 查詢中的 NULL 值SQLNull
- 去除陣列中的 null 值陣列Null
- 查詢中空值null的查理Null
- 用JS搞了一個自動翻譯，從此不再頭疼看英文書了JS
- Vue中eventbus很頭疼？我來幫你Vue
- Android 開發，你遇上 Emoji 頭疼嗎？Android
- NULL 值與索引(二)Null索引
- null(空值)小結Null
- hive NULL值影響HiveNull
- case when遇上null值Null
- servlet的設計原則，頭疼！懇請大家指點Servlet
- mysql中null與“空值”的坑MySqlNull
- MySQL裡null與空值的辨析MySqlNull
- 關於null值的小知識Null
- 工作中遇到很讓人頭疼的上司怎麼辦？
- 索引裡的NULL值與排序小記索引Null排序
- MySQL 的 NULL 值是怎麼儲存的？MySqlNull
- Linux越學越頭疼，我要怎麼辦？Linux
- 川普讓蘋果頭疼：iPhone或漲價100美元蘋果iPhone
- 前端筆記之JavaScript（六）讓人頭疼的正規表示式前端筆記JavaScript