記一次詭異的Oracle查詢轉換

chenoracle發表於2020-05-21

記一次 詭異的 Oracle 查詢轉換

說明： 今天技術群裡有人問了一個查詢語句的問題，這個語句看上去執行肯定會報錯，因為查詢的列名是不存在的，但是實際執行並沒有報錯，當然返回的結果也是錯誤的。使用 10053 看了下 SQL 執行過程中進行了比較詭異的改寫，改變了 SQL 語義，先記錄下來。

環境：

DB:Oracle 11.2.3.0

OS:Redhat 7.5

問題：

已知執行如下SQL 會報錯 ORA-00904 ，報錯原因是因為 dba_objects 表沒有 table_name 欄位。

SQL> select table_name from dba_objects;

那麼如下 SQL 會報錯嗎？

把上面報錯的語句作為子查詢。

select count (*) from dba_tables where TABLE_NAME in ( select TABLE_NAME from dba_objects );

結果是沒有報錯， 可以返回資料：

select count (*) from dba_tables where TABLE_NAME in ( select TABLE_NAME from dba_objects );

問題分析

顯然是優化器對原 SQL 進行了改寫，通過 10053 可以檢視到改寫後的 SQL 語句。

SELECT COUNT(*) "COUNT(*)"
  FROM "SYS"."USER$"  "U",
       "SYS"."TS$"    "TS",
       "SYS"."SEG$"   "S",
       "SYS"."OBJ$"   "CO",
       "SYS"."TAB$"   "T",
       "SYS"."OBJ$"   "O",
       "SYS"."OBJ$"   "CX",
       "SYS"."USER$"  "CU",
       SYS."X$KSPPCV" "KSPPCV",
       SYS."X$KSPPI"  "KSPPI"
 WHERE EXISTS ......;

由於dba_tables 和 dba_objects 屬於 sys 使用者下的檢視，引用了很多底層表， 10053 檢視轉換後的 SQL 比較複雜，很難馬上理清各個表關聯關係。

可以先排除複雜檢視的干擾，將 dba_tables和dba_objects資料插入到兩張堆表ttt1和ttt2中，在通過10053檢視ttt1和ttt2查詢改寫後的SQL，結果一目瞭然。

過程如下：

建立ttt1和ttt2表

create table ttt1 as select * from dba_tables ;

create table ttt2 as select * from dba_objects ;

同理ttt2表也沒有table_name列

select TABLE_NAME from ttt2 ;

查詢同樣沒有報錯

select count (*) from ttt1 where TABLE_NAME in ( select TABLE_NAME from ttt2 );

同樣加到子查詢裡可以正常執行

select count (*) from ttt1 where TABLE_NAME in ( select TABLE_NAME from ttt2 );

COUNT (*)

----------

2884

理論上這個SQL 會被優化器改寫，猜想可能會進行子查詢展開，例如改寫成如下 SQL ：

SELECT COUNT(*) "COUNT(*)"
  FROM "CJC"."TTT2" "TTT2", "CJC"."TTT1" "TTT1"
 WHERE "TTT1"."TABLE_NAME" = "TTT2"."TABLE_NAME";

檢視執行計劃，優化器並沒有執行子查詢展開 ( Subquery Unnesting ) ，而是將 in 條件改寫成 exists

SQL> set autotrace on

SQL> select count(*) from ttt1 where TABLE_NAME in (select TABLE_NAME from ttt2);

生成10053trace 日誌

SQL > alter session set tracefile_identifier = '10053C' ;

SQL > alter session set events '10053 trace name context forever ,level 1' ;

SQL > select count (*) from ttt1 where TABLE_NAME in ( select TABLE_NAME from ttt2 );

COUNT (*)

----------

2884

SQL > alter session set events '10053 trace name context off' ;

檢視 10053trace 日誌

[oracle@cjcos02 trace]$ pwd

/u01/app/oracle/diag/rdbms/cjcdb01/cjcdb01/trace

[oracle@cjcos02 trace]$ vim cjcdb01_ora_13682_10053C.trc

搜尋 關鍵字：Final query after transformations

---原SQL

select count (*) from ttt1 where TABLE_NAME in ( select TABLE_NAME from ttt2 );

---查詢轉換後的SQL

SELECT COUNT(*) "COUNT(*)"
  FROM "CJC"."TTT1" "TTT1"
 WHERE EXISTS (SELECT 0
          FROM "CJC"."TTT2" "TTT2"
         WHERE "TTT1"."TABLE_NAME" = "TTT1"."TABLE_NAME");

exists 表示子查詢語句返回結果不為空， where 條件成立，就會執行主sql 語句，顯然 "TTT1"."TABLE_NAME" = "TTT1"."TABLE_NAME" 條件無異於where 1=1 ，最終導致對tt1 表進行全表統計，即 select count(*) from ttt1;