Oracle 查詢轉換初探

概述

Oracle查詢轉換器的作用是把原始sql重寫為語義相同的語句，目的是為了獲得更高效的sql。
查詢轉換主要有四種技術：子查詢展開，視圖合并，謂詞推入，星型轉換。
了解查詢轉換是掌握SQL優化的基礎，本文將對這四種技術做一些簡單的介紹。

子查詢展開

子查詢展開是指優化器不再把子查詢作為獨立的單元處理，而是轉換成等價的join方式。轉換有兩種方式：一是將子查詢的結果集作為視圖，與外層表或視圖做join；二是將子查詢中的表或視圖拆出來，與外層表或視圖做join。子查詢前包含以下條件可以被展開：

·         any(= any和in等價)

·         all(<> all和not in等價)

·         exists

·         not exists

single row條件（where后面接=,<,>,<=,>=等條件）
子查詢展開的例子：

最終轉換的語句：

可以看到子查詢中的dept表被拆出來，與外部查詢塊的emp表做inner join。可以這樣轉換的前提是dept表的deptno列是唯一鍵。如果deptno列不是唯一鍵，將做semi join（即所謂的半連接）：

如果不做子查詢展開，就會走filter類型的執行計劃，并且子查詢放在最后一步執行，作用是對emp全表掃描之后的結果集進行過濾：

看一個子查詢結果集作為內聯視圖與外層查詢塊做join的例子：

執行計劃仍然走了hash join semi，要使得轉換是等價的，必須先完成子查詢中departments和locations的join，結果集作為內聯視圖VM_NSO_1，與外層查詢塊的結果集做join。

下面的執行計劃中，子查詢的結果被作為nest loop的驅動表，為保證結果集正確，需要對子查詢做hash unique去重。

如果滿足（不限于）下面的條件，子查詢展開可能導致轉換不等價，因此將不作展開：

·         where后面的連接符為=all或者<> any

·         exists后面的子查詢中帶有rownum

·         exists后面的子查詢中帶有having子句，cube子句或者rollup子句

例如下面的例子：

視圖合并

視圖合并是指對包含視圖的查詢做出轉換，使查詢只包含基表。視圖合并提供了更多的訪問路徑和join的可能性。也就是說，不做視圖合并的執行計劃包含在做了視圖合并的執行計劃中。下面的例子可以幫助理解這句話：

如果不做視圖合并，執行計劃如下：

可以看到不對emp100做視圖合并，執行計劃中出現view字樣,name列對應的就是視圖名emp100。
第二個執行計劃除了多了view的一行，訪問路徑和成本是和第一個相同的。
如果視圖定義中包含下列內容，將不能做視圖合并：

·         集合操作符(UNION,UNION ALL,INTERSECT,MINUS)

·         connect by子句

·         rownum偽列
做這些限制是為了防止視圖合并之后得到錯誤的結果集。
不能視圖合并的例子：

復雜視圖合并

復雜視圖合并技術允許對包含gourp by或者distinct的視圖做展開。

_COMPLEX_VIEW_MERGING參數控制是否激活復雜視圖合并，在9i之后默認為true，同時受OPTIMIZER_FEATURES_ENABLE參數控制：

外連接視圖合并

使用了外連接的sql中，視圖合并需要滿足下列條件之一：

·         視圖為外連接的驅動表

·         視圖的定義只包含單表
下例中視圖v1包含兩張基表，在做外連接的驅動表時發生視圖合并，做被驅動表則沒有。

謂詞推入

優化器在處理不能合并的視圖時，可以選擇將外部查詢的謂詞推入該視圖的查詢塊，或者將視圖中的謂詞拉出到主查詢。這樣更早的處理視圖的結果集，有可能會減小后續步驟操作所需的成本。
謂詞推入到視圖內部的例子：

注意到執行計劃中條件EMPLOYEE_ID<205被推入到視圖內部，將兩張基表各過濾一次，然后對結果集做union。
兩表關聯時，連接條件也可以做推入，先來看不做謂詞推入的執行計劃：

執行計劃中emp13作為驅動表與departments表做nest loop，我們使用hint強制發生謂詞推入：

可以看到執行計劃中出現PUSHED PREDICATE字樣，條件e.department_id=d.department_id被轉換成等值條件對employees表做過濾。join的謂詞推入往往產生nest loop的執行計劃（驅動表的每一行驅動被驅動表，來做謂詞的過濾）。如果是大數據集的sql，可以使用hint no_push_pred或者設置參數_push_join_predicate為false禁止謂詞推入。

星型轉換

星型轉換為提高星型查詢的效率發生，在原有條件基礎上會產生新的子查詢對事實表做過濾，然后通過對事實表相應連接列的位圖索引做位圖操作，達到過濾事實表結果集的目的。
是否開啟星型轉換受參數star_transformation_enabled控制，可以設置為：
true：優化器將考慮基于成本的星型查詢轉換；
false：禁止星型轉換；
temp_disable：優化器將考慮基于成本的星型查詢轉換，但是轉換中不會使用臨時表。
首先看一下星型轉換的例子：

注意到執行計劃首先對各個維度表過濾出結果集，然后訪問sales表連接列上的索引，做bitmap and操作之后，回表訪問數據。事實上整個過程類似于將查詢轉換為如下等價sql：

下面看一下star_transformation_enabled參數設置為true的結果：

注意到執行計劃中先對customers根據過濾條件cu.country_id =52789得到臨時表SYS_TEMP_0FD9D6601_11F1D1，后續步驟中每次需要訪問customers表時則由臨時表來替換，這也是為了總共減少所訪問的數據量所考慮。
星型轉換同樣有一些限制條件，本文暫不討論。

以上是對四類查詢轉換概念性的描述，對于具體的應用場景中的SQL要具體分析如何利用這些技術。查詢轉換還有諸如子查詢合并，連接因式分解，表擴展，表裁剪，物化視圖重寫等技術。有機會將再寫文章介紹，或者有興趣的同學自行研究。