Oracle關於nvl的一個Bug

壹頁書發表於2013-12-27
Oracle
今天王工處理了一個關於nvl的Bug。
這個問題十分詭異，發生在Oracle的特定版本，特定用法。
王工不僅明確了Bug發生的條件，還推斷了大致的原因。
王工的技術總能讓我聯想到海...

版本:            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

觸發條件:     1.NVL函式第一個引數的欄位為NOT NULL
                    2.使用FULL JOIN
                    3.NVL函式第一個引數的欄位在FULL JOIN 中被用在子查詢中

大致原因:     上述觸發條件同時成立，Oracle認為非空的欄位可以忽略NVL函式，但是FULL JOIN會新增NULL值的情況，它沒有考慮到。
                    不過僅僅限於FULL JOIN，LEFT JOIN的結果是正確的。


  1. --建立實驗表
  2. create table t1 as
  3. select rownum id from dual connect by level<5;
  4. create table t2 as
  5. select rownum id from dual connect by level<10;
  6. --整理資料
  7. delete from t2 where id <3;
  8. commit;
  9. --新增主鍵
  10. alter table t1 add constraint pk_t1 primary key (id);
  11. alter table t2 add constraint pk_t2 primary key (id);

  13. --測試語句
  14. select * from t1 full outer join
  15. (
  16.     select * from t2
  17. ) t2
  18. on t1.id=t2.id
  19. where nvl(t2.id,t1.id)=1;
檢視執行計劃，發現沒有應用NVL過濾


而同樣的語句，沒有同時達到觸發條件，或者使用NVL2等，則沒有這個問題。
例如沒有子查詢的這個條件，則結果正常。


什麼時候才能像王工一樣厲害呢

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-1065126/，如需轉載，請註明出處，否則將追究法律責任。

相關文章