oraclesql優(yōu)化中notin子句包含null返回結果為空的分析
這篇文章主要介紹“oracle sql優(yōu)化中not in子句包含null返回結果為空的分析”，在日常操作中，相信很多人在oracle sql優(yōu)化中not in子句包含null返回結果為空的分析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”oracle sql優(yōu)化中not in子句包含null返回結果為空的分析”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
成都創(chuàng)新互聯(lián)專業(yè)為企業(yè)提供龍州網站建設、龍州做網站、龍州網站設計、龍州網站制作等企業(yè)網站建設、網頁設計與制作、龍州企業(yè)網站模板建站服務，十多年龍州做網站經驗，不只是建網站，更提供有價值的思路和整體網絡服務。
創(chuàng)建測試表：
create table t_dept as select * from scott.dept; create table t_emp as select * from scott.emp; insert into t_emp(deptno,ename) values(null,'MINGSHUO'); --在emp表中插入一條數(shù)據(jù)，deptno列為null commit; |
數(shù)據(jù)結構如下：
SQL> select distinct deptno from t_emp;
DEPTNO ---------- 30
20 10 SQL> select distinct deptno from t_dept;
DEPTNO ---------- 30 20 40 10
|
此時發(fā)起一條查詢，查詢不在emp中但是在dept表中部門信息：
SQL> select * from t_dept where deptno not in (select deptno from t_emp where deptno is not null);
DEPTNO DNAME LOC ---------- -------------- ------------- 40 OPERATIONS BOSTON
|
此時是有結果返回的。
然后把子查詢中的where dept is not null去掉，再次運行查詢：
SQL> select * from t_dept where deptno not in (select deptno from t_emp);
no rows selected |
此時返回結果為空。
這里很多人存在疑惑，為什么子查詢結果集包括null就會出問題，比如t_dept.deptno為40的時候，40 not in （10,20,30,null）也成立啊。畢竟oracle查詢優(yōu)化器不如人腦智能懂得變通，查看執(zhí)行計劃就比較容易明白了。
Execution Plan ---------------------------------------------------------- Plan hash value: 2864198334
----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 4 | 172 | 5 (20)| 00:00:01 | |* 1 | HASH JOIN ANTI NA | | 4 | 172 | 5 (20)| 00:00:01 | | 2 | TABLE ACCESS FULL| T_DEPT | 4 | 120 | 2 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| T_EMP | 15 | 195 | 2 (0)| 00:00:01 | -----------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - access("DEPTNO"="DEPTNO")
Note ----- - dynamic sampling used for this statement (level=2)
|
注意到這里id 1是HASH JOIN ANTI NA。這時候就想起來了，not in是對null值敏感的。所以普通反連接是不能處理null的，因此oracle推出了改良版的能處理null的反連接方法，這種方法被稱為"Null-Aware Anti Join"。operation中的關鍵字NA就是這么來的了。
在Oracle 11gR2中，Oracl通過受隱含參數(shù)_OPTIMIZER_NULL_AWARE_ANTIJOIN控制NA，其默認值為TRUE，表示啟用Null-Aware Anti Join。
下面禁用掉，然后再觀察：
alter session set "_optimizer_null_aware_antijoin" = false; 再次執(zhí)行：select * from t_dept where deptno not in (select deptno from t_emp); |
執(zhí)行計劃如下：
Execution Plan ---------------------------------------------------------- Plan hash value: 393913035
----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 4 (0)| 00:00:01 | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| T_DEPT | 4 | 120 | 2 (0)| 00:00:01 | |* 3 | TABLE ACCESS FULL| T_EMP | 14 | 182 | 2 (0)| 00:00:01 | -----------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter( NOT EXISTS (SELECT 0 FROM "T_EMP" "T_EMP" WHERE LNNVL("DEPTNO"<>:B1))) 3 - filter(LNNVL("DEPTNO"<>:B1))
Note ----- - dynamic sampling used for this statement (level=2)
|
lnnvl用于某個語句的where子句中的條件，如果條件為true就返回false；如果條件為UNKNOWN或者false就返回true。該函數(shù)不能用于復合條件如AND, OR, or BETWEEN中。
此時比如t_dept.deptno為40的時候，（40 not in 10）and（40 not in 20）and（40 not in 30）and（40 not in null），注意這里是and“并且”，條件都需要滿足。
結果是true and true and true and false或者unknow。經過lvnnvl函數(shù)后：
false and false and false and true,結果還是false。所以自然就不會有結果了。
如果還不明白的話換個比較直觀的寫法：
SQL> select * from t_dept where deptno not in (10,20,null);
no rows selected
Execution Plan ---------------------------------------------------------- Plan hash value: 719542577
---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 30 | 2 (0)| 00:00:01 | |* 1 | TABLE ACCESS FULL| T_DEPT | 1 | 30 | 2 (0)| 00:00:01 | ----------------------------------------------------------------------------
Predicate Information (identified by operation id): ---------------------------------------------------
1 - filter("DEPTNO"<>10 AND "DEPTNO"<>20 AND "DEPTNO"<>TO_NUMBER(NULL))
Note ----- - dynamic sampling used for this statement (level=2)
|
過濾條件"DEPTNO"<>10 AND "DEPTNO"<>20 AND "DEPTNO"<>TO_NUMBER(NULL)因為最后一個and條件，整個條件恒為flase或者unkonw。
所以not in的子查詢中出現(xiàn)null值，無返回結果。
這種時候其實可以用not exists寫法和外連接代替：
not exists寫法：
其實這種寫法前面已經出現(xiàn)過了。就在禁用掉反連接之后，出現(xiàn)在fileter中的，oracle在內部改寫sql時可能就采用了這種寫法：
select * from t_dept d where not exists (select 1 from t_emp e where d.deptno = e.deptno); |
外連接的寫法：
select d.* from t_dept d, t_emp e where d.deptno=e.deptno(+) and e.deptno is null; |
同事還給我展示了丁俊的實驗，里面有復合列的討論，結論簡單明了，這里我就直接搬過來吧，如下：
/** 根據(jù)NULL的比較和邏輯運算規(guī)則，OR條件有一個為TRUE則返回TRUE，全為FALSE則結果為FALSE，其他為UNKNOWN，比如 (1,2) not in (null,2)則相當于1 <> null or 2 <> 2,那么明顯返回的結果是UNKNOWN，所以不可能為真，不返回結果，但是 (1,2) not in (null,3)相當于1 <> null or 2 <> 3,因為2<>3的已經是TRUE,所以條件為TRUE，返回結果，也就說明了為什么Q2中的 測試是那樣的結果 **/
看個簡單的結果： SQL> SELECT * FROM DUAL WHERE (1,2) not in ( (null,2) );
DUMMY ----- SQL> SELECT * FROM DUAL WHERE (1,2) not in ( (null,3) );
DUMMY ----- X
|
到此，關于“oracle sql優(yōu)化中not in子句包含null返回結果為空的分析”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續(xù)學習更多相關知識，請繼續(xù)關注創(chuàng)新互聯(lián)網站，小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
名稱欄目：oraclesql優(yōu)化中notin子句包含null返回結果為空的分析
網站地址：http://jinyejixie.com/article40/psphho.html
成都網站建設公司_創(chuàng)新互聯(lián)，為您提供網站改版、虛擬主機、網站制作、微信小程序、營銷型網站建設、外貿網站建設
聲明：本網站發(fā)布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創(chuàng)新互聯(lián)
- 網站改版如何才能把影響降到低呢? 2020-08-17
- 淺談添佰麗公司網站改版的見解 2023-02-20
- 避免陷入網站改版僵局的妙招 2023-04-30
- 網站改版后要注意的幾點 2022-07-15
- SEO人員將如何規(guī)避網站改版不當?shù)暮蠊?/a> 2022-07-04
- 之前做的網站為什么現(xiàn)在要網站改版？ 2021-12-06
- 網站改版會導致網站排名下降嗎？ 2022-11-23
- 對于網站改版，需要注意哪些地方？ 2023-03-30
- 網站改版要怎樣做？ 2021-02-09
- 網站改版設計時保留以前的優(yōu)化結果 2018-09-24
- 網站改版如何避免權重流失 2021-10-26
- 企業(yè)網站改版的頻率對網站影響有多大 2022-11-13