Oracle Database 12c查詢最佳化器的缺陷-外連線導致結果不準確

Jet_Zhang發表於2017-06-14
OracleDatabase
Oracle 12c R1目前使用的也是越來越多了，但是在實際的使用中還是有不少的問題。由於所處的行業對準確性比較敏感，而12cR1中的最佳化器確實存在著挺多的坑會導致結果不準確。其中之一就是使用外連線可能會導致結果不正確。

場景還原

建立一張測試表：
  1. create table BUG.T_BUGTEST_01(ID NUMBER,NAME VARCHAR2(30),ADDR VARCHAR2(100),PHONE VARCHAR2(30),MAIL VARCHAR2(100),AGE NUMBER);
往測試表中插入兩條資料：
  1. insert into BUG.T_BUGTEST_01 values (1,'BUG1','LBS1111111','1234567890','bug1@test.com',4);
  2. insert into BUG.T_BUGTEST_01 values (2,'BUG2','LBS2222222','1234567890','bug2@test.com',4);
  3. commit;
環境已經準備就緒了，很簡單的場景。然後我們來看看執行下面的這條SQL會發生什麼？

  1. select t1.*, p.maddr from bug.t_bugtest_01 t1,
  2. (select 'notused' maddr from dual where 1 = 0) p
  3. where t1.addr = p.maddr(+) and t1.id = 1
  4. and t1.addr in ('LBS1111111','LBS2222222')
  5. order by t1.addr;
這是一條很簡單的SQL，即時不用執行，我們也能很容易得出SQL的結果是什麼。但是實際的執行結果呢？

  1. SQL> select t1.*, p.maddr from bug.t_bugtest_01 t1,
  2.   2 (select 'notused' maddr from dual where 1 = 0) p
  3.   3 where t1.addr = p.maddr(+) and t1.id = 1
  4.   4 and t1.addr in ('LBS1111111','LBS2222222')
  5.   5 order by t1.addr;

  7. no rows selected
no rows！！！真的是no rows，不管執行多少次都是no rows。但是如果我們把最佳化器的特性進行降級：

  1. SQL> alter session set optimizer_features_enable = '11.2.0.4';

  3. Session altered.

  5. SQL> select t1.*, p.maddr from bug.t_bugtest_01 t1,
  6.   2 (select 'notused' maddr from dual where 1 = 0) p
  7.   3 where t1.addr = p.maddr(+) and t1.id = 1
  8.   4 and t1.addr in ('LBS1111111','LBS2222222')
  9.   5 order by t1.addr;

  11.         ID NAME ADDR PHONE MAIL AGE MADDR
  12. ---------- ------------------------------ ---------------------------------------------------------------------------------------------------- ------------------------------ ---------------------------------------------------------------------------------------------------- ---------- -------
  13.          1 BUG1 LBS1111111 1234567890 bug1@test.com
這個結果才是正確的。

我們簡單梳理下上面這個SQL的幾個特徵：
1、參與外連線的物件中有檢視（p）
2、檢視（p）的結果為空集
3、select選擇中有引用p的列
4、p的列參與外連線(p.maddr)
5、表t1用來和p的列進行連線的列還有額外的選擇條件（t1.addr in）

上述條件剛好觸發了12cR1的BUG 21459392。

解決方法
1、將最佳化器降低： alter session set Optimizer_features_enable = '11.2.0.4';

2、修改隱含引數： alter system set  "_optimizer_ansi_join_lateral_enhance"=false;
3、安裝補丁： Patch 21459392



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

相關文章