【DB筆試面試612】在Oracle中，查詢轉換包含哪些類型？

• 2019 年 10 月 10 日
• 筆記

題目部分

在Oracle中，查詢轉換包含哪些類型？

♣

答案部分

在Oracle數據庫中，用戶發給Oracle讓其執行的目標SQL和Oracle實際執行的SQL有可能是不同的，這是因為Oracle可能會對執行的目標SQL做等價改寫，即查詢轉換。查詢轉換（Query Transformation），也叫邏輯優化（Logical Optimization），又稱為查詢改寫（Query Rewrite）或軟優化，即查詢轉換器在邏輯上對語句做一些語義等價轉換，它是Oracle在解析目標SQL的過程中的非常重要的一步。查詢轉換能使優化器將目標SQL改寫成語義上完全等價的SQL語句但生成的執行計劃效率更高。

查詢轉換器依據特定的方式決定是否對查詢塊進行轉換。按照其所依賴的方式，轉換技術可以分為兩類：①啟發式查詢轉換（Heuristic Query Transformation），又稱為基於規則的查詢轉換（Rule Based Query Transformation），啟發式查詢轉換是基於一套規則對查詢進行轉換，一旦滿足規則所定義的條件，則對語句進行相應的轉換。啟發式查詢轉換需要從10053事件信息中查找有關查詢轉換的線索，並且許多跟蹤記錄僅能從Oracle 11g的跟蹤信息中發現。②基於代價的查詢轉換（Cost Based Query Transformation，CBQT）。基於代價的查詢轉換是否對語句進行轉換則取決於語義等價語句之間的代價對比，即採用代價最小的一種。大多數基於代價的查詢轉換可以從執行計劃的概要數據中找到線索。Oracle提供了一個隱含參數「_OPTIMIZER_COST_BASED_TRANSFORMATION」用以控制是否進行基於代價的查詢轉換，以及如何進行基於代價的查詢轉換，從而限制其對資源的消耗。

Oracle中常見的查詢轉換分類如下圖所示：

SYS@orclasm > SET PAGESIZE 9999  SYS@orclasm > SET LINE 9999  SYS@orclasm > COL NAME FORMAT A40  SYS@orclasm > COL KSPPDESC FORMAT A50  SYS@orclasm > COL KSPPSTVL FORMAT A20  SYS@orclasm > SELECT A.INDX,    2         A.KSPPINM NAME,    3         A.KSPPDESC,    4         B.KSPPSTVL    5  FROM   X$KSPPI  A,    6         X$KSPPCV B    7  WHERE  A.INDX = B.INDX    8  AND LOWER(A.KSPPINM) LIKE  LOWER('%&PARAMETER%');  Enter value for parameter: _OPTIMIZER_COST_BASED_TRANSFORMATION  old   8: AND LOWER(A.KSPPINM) LIKE  LOWER('%&PARAMETER%')  new   8: AND LOWER(A.KSPPINM) LIKE  LOWER('%_OPTIMIZER_COST_BASED_TRANSFORMATION%')          INDX NAME                                     KSPPDESC                                           KSPPSTVL  ---------- ---------------------------------------- -------------------------------------------------- --------------------        1935 _optimizer_cost_based_transformation     enables cost-based query transformation            LINEAR  

Oracle中常見的查詢轉換分類如下表所示：

關於上表中的內容需要注意以下幾點：

①　子查詢展開通常都會提高原SQL的執行效率，因為如果原SQL不做子查詢展開，那麼通常情況下該子查詢就會在其執行計劃的最後一步才被執行，並且會走FILTER類型的執行計劃，這也就意味着對於外部查詢所在結果集的每一條記錄，該子查詢就會被執行多少次，這種執行方式的執行效率通常情況不會太高，尤其在子查詢中包含兩個或兩個以上表連接時，此時做子查詢展開後的執行效率往往會比走FILTER類型的執行計劃高很多。

②　使用視圖合併技術後，優化器不再單獨為每個視圖生成子計劃，而是將視圖的查詢合併到整體查詢中去，最終為合併和整體查詢尋找到一個最優的執行計劃。

③　一般來說，如果Oracle沒有做視圖合併的話，那麼在該SQL的執行計劃中就會見到「VIEW」關鍵字，並且該關鍵字所對應的NAME列的值就是該視圖的名稱。

④　由於查詢轉換的分類非常多，本書只對常見的重要的查詢轉換做介紹，其餘的查詢轉換可以閱讀其它相關的書籍。

為了方便，使用黃瑋老師提供的一個存儲過程sql_explain：

------------------------------------------------------------  -- 《SQL優化與調優技術詳解》                             ---  -- 文件：02_01_SQL_Explain_11g.sql                       ---  -- 作者：黃瑋                                            ---  -- 網站：WWW.HelloDBA.COM                                ---  -- Coyprigh (c)：WWW.HelloDBA.COM 保留所有權利           ---  -- 描述：解析和顯示語句執行計劃                          ---  ------------------------------------------------------------    /***********************************************************  ** 用於11G                                                **  ***********************************************************/  create or replace procedure sql_explain (stmt varchar2,                                           format varchar2 default 'ADVANCED',                                           exponly boolean default true)  ------------------------------------------------------------  -- 描述：解析和顯示語句執行計劃                          ---  -- 來源：WWW.HelloDBA.COM                                ---  -- Coyprigh (c)：WWW.HelloDBA.COM 保留所有權利           ---  --                                                       ---  -- 參數描述                                              ---  --     stmt：解析或執行的語句                            ---  --     format：執行計劃輸出格式，參加DBMS_XPLAN中描述    ---  --     exponly：是否僅解析                               ---  --         TRUE：僅調用EXPLAIN PLAN命令解析語句          ---  --        FALSE：執行語句後從緩存獲得執行計劃            ---  ------------------------------------------------------------    AUTHID CURRENT_USER  as    c number;    r number;    sqlid varchar2(100);    childnum number;  begin    dbms_output.enable(50000);    if exponly then      execute immediate 'explain plan for '||stmt;      for xpl_rec in ( select * from table(dbms_xplan.display(null,null,format)) ) loop          dbms_output.put_line(xpl_rec.plan_table_output);      end loop;    else       c := dbms_sql.open_cursor;       dbms_sql.parse(c,stmt,dbms_sql.native);       r := dbms_sql.execute_and_fetch(c);       loop         exit when r <= 0;         r := dbms_sql.fetch_rows(c);       end loop;      select distinct p.sql_id, p.child_number into sqlid, childnum      from v$sql_cursor sc, v$sql_plan p, v$open_cursor c, v$sqlarea q      where p.address=sc.PARENT_HANDLE and p.sql_id=q.sql_id and c.sql_id = q.sql_id and c.sid = SYS_CONTEXT('USERENV','SID') and q.sql_text like substr(stmt,0,30)||chr(37) and rownum<=1;      --select distinct s.sql_id, s.child_number into sqlid, childnum from v$sql_plan s, v$sql_cursor c where s.address=c.PARENT_HANDLE and c.curno=c and rownum<=1;      dbms_sql.close_cursor(c);      for xpl_rec in ( select * from table(dbms_xplan.display_cursor(sqlid,childnum,format)) ) loop          dbms_output.put_line(xpl_rec.plan_table_output);      end loop;    end if;    rollback;  end;  /    grant execute on sql_explain to public;  create or replace public synonym sql_explain for sys.sql_explain;  

本文選自《Oracle程序員面試筆試寶典》，作者：李華榮。