本文描述了Oracle 的查詢優化程序，它是數據庫的關鍵組件，能讓Oracle 的用戶獲得極佳的執行性能。Oracle 的查詢優化技術在功能上無與倫比，本文詳細討論了查詢優化的所有重要領域。

　　簡介

　　什么是查詢優化程序？

　　查詢優化對于關系數據庫的性能，特別是對于執行復雜SQL 語句的性能而言至關重要。查詢優化程序確定執行每一次查詢的最佳策略。

　　例如，查詢優化程序選擇對于指定的查詢是否使用索引，以及在聯接多個表時采用哪一種聯接技術。這類決策對SQL 語句的執行性能有很大的影響，查詢優化對于每一種應用程序都是關鍵技術，應用程序涉及的范圍從操作系統到數據倉庫，從分析系統到內容管理系統。查詢優化程序對于應用程序和最終用戶是完全透明的。

　　由于應用程序可能生成非常復雜的SQL 語句, 查詢優化程序必須精心構建、功能強大，以保障良好的執行性能。例如，查詢優化程序可轉換SQL 語句，使復雜的語句轉換成為等價的但執行性能更好的SQL 語句。查詢優化程序的典型特征是基于開銷。在基于開銷的優化策略中，對于給定查詢生成多個執行計劃，然后對每個計劃估算開銷。查詢優化程序選用估算開銷最低的計劃。

　　Oracle 在查詢優化方面提供了什么？

　　Oracle 的優化程序可稱是業界最成功的優化程序。基于開銷的優化程序自1992 年隨Oracle7 推出后，通過10 年的豐富的實際用戶經驗，不斷得到提高和改進。好的查詢優化程序不是基于純粹的理論假設及謂詞在實驗室中開發出來的，而是通過適合實際用戶需求開發和磨合出來的。

　　Oracle 的查詢優化程序比任何其他查詢優化程序在數據庫應用程序的應用都要多，而且Oracle 的優化程序一直由于實際應用的反饋而得到改進。

　　Oracle 的優化程序包含4 大主要部分（本文將在以下章節詳細討論這些部分）：

　　SQL 語句轉換：在查詢優化中Oracle 使用一系列精深技術對SQL 語句進行轉換。查詢優化的這一步驟的目的是將原有的SQL 語句轉換成為語義相同而處理效率更高的SQL 語句。

　　執行計劃選擇：對于每個SQL 語句, 優化程序選擇一個執行計劃（可使用Oracle 的EXPLAIN PLAN 工具或通過Oracle 的“v$sql_plan” 視圖查看）。執行計劃描述了執行SQL 時的所有步驟，如訪問表的順序；如何將這些表聯接在一起；以及是否通過索引來訪問這些表。優化程序為每個SQL 語句設計許多可能的執行計劃，并選出最好的一個。

　　開銷模型與統計：Oracle 的優化程序依賴于執行SQL 語句的所有單個操作的開銷估算。想要優化程序能選出最好的執行計劃，需要最好的開銷估算方法。開銷估算需要詳細了解某些知識，這些知識包括：明白每個查詢所需的I/O、CPU 和內存資源以及數據庫對象相關的統計信息（表、索引和物化視圖），還有有關硬件服務器平臺的性能信息。收集這些統計和性能信息的過程應高效并且高度自動化。

　　動態運行時間優化：并不是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 E.DEPTNO = D.DEPTNO;
　　SELECT ENAME, DNAME FROM TEST_VIEW WHERE SAL > 10000;

　　如果不加任何轉換，處理該查詢的唯一方法是將EMP 的所有行聯接到DEPT 表的所有行，然后篩選有適當的SAL 的值的那些行。

　　如果使用視圖合并，上述查詢可以轉換為：

　　SELECT ENAME, DNAME FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.SAL > 10000;

　　處理該轉換后的查詢時，可以在聯接EMP 和DEPT 表前使用謂詞‘SAL>10000’。這一轉換由于減少了聯接的數據量而大大提高了查詢的執行性能。即便在這樣一個非常簡單的示例里，查詢轉換的益處和重要性也顯而易見。

　　復雜視圖合并

　　許多視圖合并操作都是直截了當的，如以上示例。但是，較復雜的視圖，如包含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 DEPT.NAME, AVG_SAL_DEPT FROM DEPT, AVG_SAL_VIEW WHERE DEPT.DEPTNO = AVG_SAL_VIEW.DEPTNO AND DEPT.LOC = 'OAKLAND'

　　可以轉換為：

　　SELECT DEPT.NAME, AVG(SAL) FROM DEPT, EMP WHERE DEPT.DEPTNO = EMP.DEPTNO AND DEPT.LOC = 'OAKLAND' GROUP BY DEPT.ROWID, DEPT.NAME

　　該特殊轉換的執行性能優點立即顯現：該轉換把EMP 數據在分組聚合前進行聯接和篩選，而不是在聯接前將EMP 表的所有數據分組聚合。

　　子查詢“展平”

　　Oracle 有一些轉換能將不同類型的子查詢轉變為聯接、半聯接或反聯接。作為該領域內的技術示例，我們來看下面這個查詢，找出有工資超過10000 的員工的那些部門：

　　SELECT D.DNAME FROM DEPT D WHERE D.DEPTNO IN (SELECT E.DEPTNO FROM EMP E WHERE E.SAL > 10000)

　　存在一系列可以優化本查詢的執行計劃。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 D.DEPTNO E.DEPTNO AND E.SAL > 10000;

　　另一個可能的計劃是優化程序可以決定將DEPT 表作為聯接的內表。在這種情況下，查詢作為通常的聯接來執行，但對EMP 表進行特別排序，以消除冗余的部門號：

　　OPERATION OBJECT_NAME OPTIONS
　　SELECT STATEMENT
　　HASH JOIN
　　SORT UNIQUE
　　TABLE ACCESS EMP FULL
　　TABLE ACCESS DEPT FULL

　　轉換后的SQL 語句為：

　　SELECT D.DNAME FROM (SELECT DISTINCT DEPTNO FROM EMP) E, DEPT D WHERE E.DEPTNO = D.DEPTNO AND E.SAL > 10000;

　　與視圖合并一樣，子查詢展平也是獲得良好查詢執行性能的基本優化辦法。

　　傳遞謂詞生成

　　在某些查詢中，由于表間的聯接關系，一個表中的謂詞可以轉化為另一個表中的謂詞。Oracle 會以這種方式演繹出新的謂詞，這類謂詞被稱為傳遞謂詞。例如，來看一個查詢，找出定貨當天運出的所有商品：

　　SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER, LINEITEM WHERE O_ORDERKEY = L_ORDERKEY AND O_ORDERDATE = L_SHIPDATE AND O_ORDERDATE BETWEEN '1-JAN-2002' AND '31-JAN-2002'

　　利用傳遞性，該ORDER 表中的謂詞也可以用于LINEITEM 表：

　　SELECT COUNT(DISTINCT O_ORDERKEY) FROM ORDER, LINEITEM WHERE