您好,登錄后才能下訂單哦!
這篇文章主要介紹“oracle sql優(yōu)化中not in子句包含null返回結(jié)果為空的分析”,在日常操作中,相信很多人在oracle sql優(yōu)化中not in子句包含null返回結(jié)果為空的分析問(wèn)題上存在疑惑,小編查閱了各式資料,整理出簡(jiǎn)單好用的操作方法,希望對(duì)大家解答”oracle sql優(yōu)化中not in子句包含null返回結(jié)果為空的分析”的疑惑有所幫助!接下來(lái),請(qǐng)跟著小編一起來(lái)學(xué)習(xí)吧!
創(chuàng)建測(cè)試表:
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ù)結(jié)構(gòu)如下:
SQL> select distinct deptno from t_emp;
DEPTNO ---------- 30
20 10 SQL> select distinct deptno from t_dept;
DEPTNO ---------- 30 20 40 10
|
此時(shí)發(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
|
此時(shí)是有結(jié)果返回的。
然后把子查詢中的where dept is not null去掉,再次運(yùn)行查詢:
SQL> select * from t_dept where deptno not in (select deptno from t_emp);
no rows selected |
此時(shí)返回結(jié)果為空。
這里很多人存在疑惑,為什么子查詢結(jié)果集包括null就會(huì)出問(wèn)題,比如t_dept.deptno為40的時(shí)候,40 not in (10,20,30,null)也成立啊。畢竟oracle查詢優(yōu)化器不如人腦智能懂得變通,查看執(zhí)行計(jì)劃就比較容易明白了。
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。這時(shí)候就想起來(lái)了,not in是對(duì)null值敏感的。所以普通反連接是不能處理null的,因此oracle推出了改良版的能處理null的反連接方法,這種方法被稱為"Null-Aware Anti Join"。operation中的關(guān)鍵字NA就是這么來(lái)的了。
在Oracle 11gR2中,Oracl通過(guò)受隱含參數(shù)_OPTIMIZER_NULL_AWARE_ANTIJOIN控制NA,其默認(rèn)值為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í)行計(jì)劃如下:
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用于某個(gè)語(yǔ)句的where子句中的條件,如果條件為true就返回false;如果條件為UNKNOWN或者false就返回true。該函數(shù)不能用于復(fù)合條件如AND, OR, or BETWEEN中。
此時(shí)比如t_dept.deptno為40的時(shí)候,(40 not in 10)and(40 not in 20)and(40 not in 30)and(40 not in null),注意這里是and“并且”,條件都需要滿足。
結(jié)果是true and true and true and false或者unknow。經(jīng)過(guò)lvnnvl函數(shù)后:
false and false and false and true,結(jié)果還是false。所以自然就不會(huì)有結(jié)果了。
如果還不明白的話換個(gè)比較直觀的寫法:
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)
|
過(guò)濾條件"DEPTNO"<>10 AND "DEPTNO"<>20 AND "DEPTNO"<>TO_NUMBER(NULL)因?yàn)樽詈笠粋€(gè)and條件,整個(gè)條件恒為flase或者unkonw。
所以not in的子查詢中出現(xiàn)null值,無(wú)返回結(jié)果。
這種時(shí)候其實(shí)可以用not exists寫法和外連接代替:
not exists寫法:
其實(shí)這種寫法前面已經(jīng)出現(xiàn)過(guò)了。就在禁用掉反連接之后,出現(xiàn)在fileter中的,oracle在內(nèi)部改寫sql時(shí)可能就采用了這種寫法:
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; |
同事還給我展示了丁俊的實(shí)驗(yàn),里面有復(fù)合列的討論,結(jié)論簡(jiǎn)單明了,這里我就直接搬過(guò)來(lái)吧,如下:
/** 根據(jù)NULL的比較和邏輯運(yùn)算規(guī)則,OR條件有一個(gè)為TRUE則返回TRUE,全為FALSE則結(jié)果為FALSE,其他為UNKNOWN,比如 (1,2) not in (null,2)則相當(dāng)于1 <> null or 2 <> 2,那么明顯返回的結(jié)果是UNKNOWN,所以不可能為真,不返回結(jié)果,但是 (1,2) not in (null,3)相當(dāng)于1 <> null or 2 <> 3,因?yàn)?<>3的已經(jīng)是TRUE,所以條件為TRUE,返回結(jié)果,也就說(shuō)明了為什么Q2中的 測(cè)試是那樣的結(jié)果 **/
看個(gè)簡(jiǎn)單的結(jié)果: 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
|
到此,關(guān)于“oracle sql優(yōu)化中not in子句包含null返回結(jié)果為空的分析”的學(xué)習(xí)就結(jié)束了,希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí),快去試試吧!若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí),請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站,小編會(huì)繼續(xù)努力為大家?guī)?lái)更多實(shí)用的文章!
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。