最近在看一個哥們最佳化sql裡面,使用到了幾個特殊的hint，但我總是對此不解，所以針對此問題自己做了一些測試(參考了一些資料)
1. no_unnest, unnest
unnest我們稱為對子查詢展開，顧名思義，就是別讓子查詢孤單地嵌套(nest)在裡面。
所以un_unnest雙重否定代表肯定，即讓子查詢不展開，讓它嵌套(nest)在裡面。
如果一個查詢中的where 中出現 view 或者 子查詢。那麼oracle的CBO在解析著這個SQL之前要做轉換，把VIEW或者子查詢"開啟"~ 這個操作就叫做unnest~ 然後可以把主查詢和子查詢中的表通過表串連的方式，產生執行計畫。你可以使用NO_UNNEST這個hint來告訴CBO，不要“開啟” VIEW或者子查詢~ 這樣VIEW或者子查詢只能被當作一個獨立查詢，來進行解析，裡面的表無法和主查詢中的表進行串連
現做一個簡單的實驗：
SQL> select count(*) from dba_objects;
COUNT(*)
----------
60494
這裡子查詢自動延伸（unnest），即TMP_LIUHC_1和TMP_LIUHC_2 hash join在一起。
接下來如果我們不希望TMP_LIUHC_2展開，想先讓它單獨的執行完，然後再來和外部查詢進行一種叫做FILTER的操作。
那麼我們加入hint no_unnest:
所以，什麼時候該用no_unnest使得子查詢能夠獨立的執行完畢之後再跟外圍的查詢做FILTER？
首先，子查詢的返回結果集應該較小，然後外圍查詢的輸入的distinct value也應該較小。
2.push_subq
如果說no_unnest是為了讓子查詢不展開，獨立的完成，那麼push_subq就是為了讓子查詢最先進行join。
所以，這個hint其實是控制的join的順序。SQL> create table tmp_liuhc_3 as select * from dba_objects;
Table created
SQL> create table tmp_liuhc_4 as select * from dba_objects;
Table created
SQL> analyze table tmp_liuhc_3 compute statistics;
Table analyzed
SQL> analyze table tmp_liuhc_4 compute statistics;
Table analyzed
SQL> create index idx_tmp_liuhc_3 on tmp_liuhc_3(object_id);
Index created
3. push_pred對應push_pred這個hint，首先要搞清楚mergeable view和unmergeable view的區別。
這個在concept上有明確解釋：
Mergeable and Unmergeable ViewsThe optimizer can merge a view into a referencing query block when the view has one or more base tables, provided the view does not
contain:
set operators (UNION, UNION ALL, INTERSECT, MINUS)
a CONNECT BY clause
a ROWNUM pseudocolumn
- aggregate functions (AVG, COUNT, MAX, MIN, SUM) in the select list
When a view contains one of the following structures, it can be merged into a referencing query block only if
complex view merging is enabled (as described below):
a GROUP BY clause
- a DISTINCT operator in the select list
View merging is not possible for a view that has multiple base tables if it is on the right side of an outer join. If a view on the right side of an outer join has only one base table, however, the optimizer can use complex view merging even if an expression
in the view can return a non-null value for a NULL. See
"Views in Outer Joins" for more information.
這裡在最後，我們發現一個unmergeable view的一種情況就是view在outer join的右側。
對於這種情況，我們熟知的merge hint也無效。對於這樣一個簡單的查詢，可見謂詞TMP_LIUHC_3.object_name=tmp_liuhc_view.object_name被merge到了view中，那麼我把tmp_liuhc_view放到outer join的右側，這是tmp_liuhc_view就屬於unmergeable view了，最佳化器預設無法將謂詞merge進這個tmp_liuhc_view中，於是就看到了tmp_liuhc_view單獨先執行：
那麼我們使用hint push_pred強制最佳化器將謂詞merge進view中，可見到“VIEW PUSHED PREDICATE”：雖然merge hint會有同樣的效果，但是對於這種unmergeable view來說，merge hint是無效的。可見，對於此種身處outger join右側的view來說，merge hint已經無能為力了。
綜上，對於大家比較容易混淆的三個hint：
no_unnest/unnest是針對子查詢是否展開的，push_subq是針對子查詢的串連順序的，push_pred則是針對unmergeable view使用外部查詢謂詞。
