子查詢，是sql中常見的一種寫法。對于優化器來說，子查詢是較難優化的部分。oracle提供了多種方式，對子查詢進行查詢轉換。
一、子查詢推進
子查詢推進（又稱子查詢推入）是指優化器将子查詢提前進行評估，使得優化器可以更早地介入優化以獲得更優質的執行計劃。這個技術可以通過提示push_subq/no_push_subq控制。下面通過一個示例看看結果。
執行以下語句：
執行計劃如下：
注：在這個語句中，我們通過提示強制不使用子查詢推進技術。由執行計劃可見，執行是按照t_objects和t_usres進行的一個索引的嵌套循環。
使用子查詢推進：
在這個示例中，oracle使用了子查詢推入技術，且可以在outline中看到push_subq字樣。從執行計劃可見，沒有出現兩表關聯，提前處理了子查詢，生成max created，然後全表掃描t_objects進行條件過濾，顯然這種方式效率更高。
二、子查詢嵌套、展開
子查詢解嵌套是指優化器将子查詢展開，和外部的查詢進行關聯、合并，進而得到更優的執行計劃。可以通過unnest/no_unnest提示控制是否進行解嵌套。采用這種技術通常可以提高執行效率，原因是如果不解嵌套，子查詢往往是最後執行的，作為filter條件來過濾外部查詢；而一旦展開，優化器就可以選擇表關聯等更高效的執行方式，以提高效率。下面通過幾個示例說明各種解嵌套的形式。
先看第一個示例：
在這個示例中，對exists的子查詢進行了解嵌套，然後選擇了半連接配接（semi join）的關聯方式。
再來看一個示例。
在這個示例中，對not exists的子查詢進行了解嵌套，然後選擇了反連接配接（anti join）的關聯方式。
三、子查詢分解
子查詢分解是由with建立的複雜查詢語句并存儲在臨時表中，可按照與一般表相同的方式使用該臨時表的功能。這種方式可以把一個複雜的查詢分成很多簡單的部分，并讓優化器去決定是産生中間資料集還是建構該查詢複雜的擴充形式并對其進行優化。這種方式的優點在于，使用with子句的子查詢在複雜查詢語句中隻需要執行一次，但結果可以在同一個查詢語句中被多次使用。缺點在于，這種方式不允許語句變形，是以無效的情況較多。
下面看一個示例。
子查詢定義為user_obj，在執行計劃中以一個視圖的形式（id=2的步驟）出現，并與t_usres進行了哈希關聯。
上述過程并沒有生成臨時表，可通過一個提示materialize強制優化器建立臨時表。
引入了materialize提示後，由id=2步驟可見，系統生成了一個臨時表sys_temp_xxx，并由這個表在後面與t_users進行了關聯查詢。
四、子查詢合并
在語義等價的前提下，如果多個子查詢産生的結果集相同，則優化器可以使用這種技術将多個子查詢合并為一個子查詢。這樣的好處在于減少多次掃描産生的開銷。可以通過no_coalesce_sq/coalesce_sq提示來控制。下面看個示例：
在這個查詢中，外部對t_tables表的查詢要同時滿足sub1和sub2兩個子查詢，而sub1在語義上又是sub2的子集，是以優化器将兩個子查詢進行了合并(隻進行一次對t_tablespaces表的掃描)，然後與外部表t_tables進行半連接配接。
那麼如果語義不等價又會怎麼樣呢？
在這個查詢語句中，外部查詢要滿足兩個子查詢—sub1和sub2，但兩者條件不同，不能簡單合并。是以在執行計劃中，分别對兩者進行了掃描（直覺感覺就是對t_tablespaces進行了兩次掃描），然後再做關聯查詢。
五、子查詢優化
子查詢實體化是指在上面with定義的查詢中，将查詢結果寫入一張臨時表中，後續的查詢直接利用臨時表中的資料。可以通過materialize提示來控制。下面看個示例。
在id=2的步驟中生成了一張臨時表sys_temp_xxx，并且這個臨時表在後面會被直接使用。如果去掉提示會怎樣呢？
此時不再生成臨時表，直接解嵌套執行。
-----the end
<b>本文來自雲栖社群合作夥伴“dbgeek”</b>