Oracle9i 的查詢優化

　　執行概要
　　本文描述了Oracle 的查詢優化程序它是數據庫的關鍵組件能讓Oracle 的用戶獲得極佳的執行性能Oracle 的查詢優化技術在功能上無與倫比本文詳細討論了查詢優化的所有重要領域
　　簡介
　　什麼是查詢優化程序？
　　查詢優化對於關系數據庫的性能特別是對於執行復雜SQL 語句的性能而言至關重要查詢優化程序確定執行每一次查詢的最佳策略
　　例如查詢優化程序選擇對於指定的查詢是否使用索引以及在聯接多個表時采用哪一種聯接技術這類決策對SQL 語句的執行性能有很大的影響查詢優化對於每一種應用程序都是關鍵技術應用程序涉及的范圍從操作系統到數據倉庫從分析系統到內容管理系統查詢優化程序對於應用程序和最終用戶是完全透明的
　　由於應用程序可能生成非常復雜的SQL 語句 查詢優化程序必須精心構建功能強大以保障良好的執行性能例如查詢優化程序可轉換SQL 語句使復雜的語句轉換成為等價的但執行性能更好的SQL 語句查詢優化程序的典型特征是基於開銷在基於開銷的優化策略中對於給定查詢生成多個執行計劃然後對每個計劃估算開銷查詢優化程序選用估算開銷最低的計劃
　　Oracle 在查詢優化方面提供了什麼?
　　Oracle 的優化程序可稱是業界最成功的優化程序基於開銷的優化程序自 年隨Oracle 推出後通過 年的豐富的實際用戶經驗不斷得到提高和改進好的查詢優化程序不是基於純粹的理論假設及謂詞在實驗室中開發出來的而是通過適合實際用戶需求開發和磨合出來的
　　Oracle 的查詢優化程序比任何其他查詢優化程序在數據庫應用程序的應用都要多而且Oracle 的優化程序一直由於實際應用的反饋而得到改進
　　Oracle 的優化程序包含 大主要部分（本文將在以下章節詳細討論這些部分）
　　SQL 語句轉換在查詢優化中Oracle 使用一系列精深技術對SQL 語句進行轉換查詢優化的這一步驟的目的是將原有的SQL 語句轉換成為語義相同而處理效率更高的SQL 語句
　　執行計劃選擇對於每個SQL 語句 優化程序選擇一個執行計劃（可使用Oracle 的EXPLAIN PLAN 工具或通過Oracle 的v$sql_plan 視圖查看）執行計劃描述了執行SQL 時的所有步驟如訪問表的順序如何將這些表聯接在一起以及是否通過索引來訪問這些表優化程序為每個SQL 語句設計許多可能的執行計劃並選出最好的一個
　　開銷模型與統計Oracle 的優化程序依賴於執行SQL 語句的所有單個操作的開銷估算想要優化程序能選出最好的執行計劃需要最好的開銷估算方法開銷估算需要詳細了解某些知識這些知識包括明白每個查詢所需的I/OCPU 和內存資源以及數據庫對象相關的統計信息（表索引和物化視圖）還有有關硬件服務器平台的性能信息收集這些統計和性能信息的過程應高效並且高度自動化
　　動態運行時間優化並不是SQL 執行的每個方面都可以事先進行優化Oracle 因此要根據當前數據庫負載對查詢處理策略進行動態調整該動態優化的目標是獲得優化的執行性能即使每個查詢可能不能夠獲得理想的CPU 或內存資源Oracle 另有一個原來的優化程序即基於規則的優化程序該優化程序僅向後兼容在Oracle 的下個版本將不再得到支持絕大多數Oracle 用戶目前使用基於開銷的優化程序所有主要的應用程序供應商（如Oracle 應用程序SAP 和Peoplesoft僅列出這幾家）以及大量近來開發的客戶應用程序都使用基於開銷的優化程序來獲得優良的執行性能故本文僅講述基於開銷的優化程序
　　SQL 語句轉換
　　使用SQL 語句表示復雜查詢可以有多種方式提交到數據庫的SQL 語句類型通常是最終用戶或應用程序可以最簡單的方式生成的SQL 類型但是這些人工編寫或機器生成的查詢公式不一定是執行查詢最高效的SQL 語句例如由應用程序生成的查詢通常含有一些無關緊要的條件這些條件可以去掉或者有些從某查詢謂詞出的附加條件應當添加到該SQL 語句中SQL 轉換語句的目的是將給定的SQL 語句轉換成語義相同（即返回相同結果的SQL 語句）並且性能更好的SQL 語句
　　所有的這些轉換對應用程序及最終用戶完全透明SQL 語句轉換在查詢優化過程中自動實現
　　Oracle 實現了多種SQL 語句轉換這些轉換大概可分成兩類
　　試探查詢轉換在可能的情況下對進來的SQL 語句都會進行這種轉換這種轉換能夠提供相同或較好的查詢性能所以Oracle 知道實施這種轉換不會降低執行性能 基於開銷的查
　　詢轉換Oracle 使用基於開銷的方法進行幾類查詢轉換借助這種方法轉換後的查詢會與原查詢相比較然後Oracle 的優化程序從中選出最佳執行策略
　　以下部分將討論Oracle 轉換技術的幾個示例這些示例並非是權威的僅用於幫助讀者理解該關鍵轉換技術及其益處
　　試探查詢轉換
　　簡單視圖合並
　　可能最簡單的查詢轉換是視圖合並對於包含視圖的查詢通常可以通過把視圖定義與查詢合並來將視圖從查詢中去掉例如請看下面的非常簡單的視圖及查詢
　　CREATE VIEW TEST_VIEW AS SELECT ENAME DNAME SAL FROM EMP E DEPT D WHERE EDEPTNO = DDEPTNO;
　　SELECT ENAME DNAME FROM TEST_VIEW WHERE SAL > ;
　　如果不加任何轉換處理該查詢的唯一方法是將EMP 的所有行聯接到DEPT 表的所有行然後篩選有適當的SAL 的值的那些行
　　如果使用視圖合並上述查詢可以轉換為
　　SELECT ENAME DNAME FROM EMP E DEPT D WHERE EDEPTNO = DDEPTNO AND ESAL > ;
　　處理該轉換後的查詢時可以在聯接EMP 和DEPT 表前使用謂詞SAL>這一轉換由於減少了聯接的數據量而大大提高了查詢的執行性能即便在這樣一個非常簡單的示例裡查詢轉換的益處和重要性也顯而易見
　　復雜視圖合並
　　許多視圖合並操作都是直截了當的如以上示例但是較復雜的視圖如包含GROUP BY 或DISTINCT 操作符的視圖合並起來就不那麼容易了Oracle 為合並這類復雜視圖提供了一些高級技術
　　請看以下帶有GROUP BY 語句的視圖在該示例中視圖計算每個部門的平均工資
　　CREATE VIEW AVG_SAL_VIEW AS SELECT DEPTNO AVG(SAL) AVG_SAL_DEPT FROM EMP GROUP BY DEPTNO
　　查詢的目的是要找出Oakland 每個部門的平均工資
　　SELECT DEPTNAME AVG_SAL_DEPT FROM DEPT AVG_SAL_VIEW WHERE DEPTDEPTNO = AVG_SAL_VIEWDEPTNO AND DEPTLOC = OAKLAND
　　可以轉換為
　　SELECT DEPTNAME AVG(SAL) FROM DEPT EMP WHERE DEPTDEPTNO = EMPDEPTNO AND DEPTLOC = OAKLAND GROUP BY DEPTROWID DEPTNAME
　　該特殊轉換的執行性能優點立即顯現該轉換把EMP 數據在分組聚合前進行聯接和篩選而不是在聯接前將EMP 表的所有數據分組聚合
　　子查詢展平
　　Oracle 有一些轉換能將不同類型的子查詢轉變為聯接半聯接或反聯接作為該領域內的技術示例我們來看下面這個查詢找出有工資超過 的員工的那些部門
　　SELECT DDNAME FROM DEPT D WHERE DDEPTNO IN (SELECT EDEPTNO FROM EMP E WHERE ESAL > )
　　存在一系列可以優化本查詢的執行計劃Oracle 會考慮這些可能的不同轉換基於開銷選出最佳計劃
　　如果不進行任何轉換這一查詢的執行計劃如下
　　OPERATION OBJECT_NAME OPTIONS
　　SELECT STATEMENT
　　FILTER
　　TABLE ACCESS DEPT FULL
　　TABLE ACCESS EMP FULL
　　按照該執行計劃將掃描DEPT 表的每一行查找所有滿足子查詢條件的EMP 記錄通常這不是一種高效的執行策略然而查詢轉換可以實現效率更高的計劃
　　該查詢的可能計劃之一是將查詢作為半聯接來執行半聯接是一種特殊類型的聯接它消除了聯接中來自內表的冗余值（這實際上就是該子查詢的原本的語義）在該示例中優化程序選擇了一個散列半聯接盡管Oracle 也支持排序合並以及嵌套循環半聯接
　　OPERATION OBJECT_NAME OPTIONS
　　SELECT STATEMENT
　　HASH JOIN SEMI
　　TABLE ACCESS DEPT FULL
　　TABLE ACCESS EMP FULL
　　由於SQL 沒有用於半聯接的直接語法此轉換過的查詢不能使用標准的SQL 來表示但是轉換後的偽SQL 將是
　　SELECT DNAME FROM EMP E DEPT D WHERE DDEPTNO EDEPTNO AND ESAL > ;
　　另一個可能的計劃是優化程序可以決定將DEPT 表作為聯接的內表在這種情況下查詢作為通常的聯接來執行但對EMP 表進行特別排序以消除冗余的部門號
　　OPERATION OBJECT_NAME OPTIONS
　　SELECT STATEMENT
　　HASH JOIN
　　SORT UNIQUE
　　TABLE ACCESS EMP FULL
　　TABLE ACCESS DEPT FULL
　　轉換後的SQL 語句為
　　SELECT DDNAME FROM (SELECT DISTINCT DEPTNO FROM EMP) E DEPT D WHERE EDEPTNO = DDEPTNO AND ESAL > ;
　　與視圖合並一樣子查詢展平也是獲得良好查詢執行性能的基本優化辦法
　　傳遞謂詞生成
　　在某些查詢中由於表間的聯接關系一個表中的謂詞可以轉化為另一個表中的謂詞Oracle 會以這種方式演繹出新的謂詞這類謂詞被稱為傳遞謂詞例如來看一個查詢找出定貨當天運出的所有商品
　　SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER LINEITEM WHERE O_ORDERKEY = L_ORDERKEY AND O_ORDERDATE = L_SHIPDATE AND O_ORDERDATE BETWEEN JAN AND JAN
　　利用傳遞性該ORDER 表中的謂詞也可以用於LINEITEM 表
　　SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER LINEITEM WHERE
From:http://tw.wingwit.com/Article/program/Oracle/201311/18405.html