使用ordered提示指定連接順序

你能夠設(shè)定優(yōu)化器所執(zhí)行的評估數(shù)目的上限。但是即使采用有很高價值的排列評估，我們?nèi)匀粨碛惺箖?yōu)化器可以盡早地放棄復(fù)雜的查詢的重要機(jī)會?；叵胍幌潞?5個連接查詢的例子，它將有超過1萬億種的連接組合。如果優(yōu)化器在評估了80,000個組合后停止，那么它才僅僅評估了0.000006%的可能組合，而且或許還沒有為這個巨大的查詢找到最佳的連接順序。

在Oracle SQL中解決此問題的最好的方法是手工指定表的連接順序。為了盡快創(chuàng)建最小的解決方案集，這里所遵循的規(guī)則是將表結(jié)合起來，通常優(yōu)先使用限制最嚴(yán)格的WHERE子句來連接表。

下面的代碼是一個查詢執(zhí)行計(jì)劃的例子，該例子在emp表的關(guān)聯(lián)查詢上強(qiáng)制執(zhí)行了嵌套的循環(huán)連接。注意，我已經(jīng)使用了ordered提示來直接最優(yōu)化表的評估順序，最終它們表現(xiàn)在WHERE子句上。

select /*+ ordered use_nl(bonus) parallel(e, 4) */
            　　
            e.ename,
            　　
            hiredate,
            　　
            b.comm.
            　　
            from
            　　
            emp e,
            　　
            bonus b
            　　
            where
            　　
            e.ename = b.ename

這個例子要求優(yōu)化器按順序連接在SQL語句的FROM子句中指定的表，在FROM子句中的第一個表指定了驅(qū)動表。ordered提示通常被用來與其它的提示聯(lián)合起來來保證采用正確的順序連接多個表。它的用途更多的是在扭轉(zhuǎn)連接表數(shù)在四個以上的數(shù)據(jù)倉庫的查詢方面。

另外一個例子，下面的查詢使用ordered提示按照指定的順序來連接表：emp、dept、sal，最后是bonus。我通過指定emp到dept使用哈希連接和sal到bonus使用嵌套循環(huán)連接，來進(jìn)一步精煉執(zhí)行計(jì)劃。

select /*+ ordered use_hash (emp, dept) use_nl (sal, bonus) */
            　　
            from
            　　
            emp,
            　　
            dept,
            　　
            sal,
            　　
            bonus
            　　
            where .

建議:

實(shí)際上，更有效率的做法是在產(chǎn)品環(huán)境中減小optimizer_max_permutations參數(shù)的大小，并且總是使用穩(wěn)定的優(yōu)化計(jì)劃或存儲輪廓來防止出現(xiàn)耗時的含有大量連接的查詢。一旦找到最佳的連接順序，您就可以通過增加ordered提示到當(dāng)前的查詢中，并保存它的存儲輪廓，來為這些表手工指定連接順序，從而使其持久化。

當(dāng)你打算使用優(yōu)化器來穩(wěn)定計(jì)劃，則可以照下面的方法使執(zhí)行計(jì)劃持久化，臨時將optimizer_search_limit設(shè)置為查詢中的表的數(shù)目，從而允許優(yōu)化器考慮所有可能的連接順序。然后，通過重新編排WHERE子句中表的名字，并使用ordered提示，與存儲輪廓一起使變更持久化，來調(diào)整查詢。在查詢中包含四個以上的表時，ordered提示和存儲輪廓將排除耗時的評估SQL連接順序解析的任務(wù)，從而提高查詢的速度。

一旦檢測到最佳的連接順序，我們就可以使用ordered提示來重載optimizer_search_limit和optimizer_max_permutations參數(shù)。ordered提示要求表按照它們出現(xiàn)在FROM子句中的順序進(jìn)行連接，所以優(yōu)化器沒有加入描述。

作為一個Oracle專業(yè)人員，你應(yīng)該知道在SQL語句第一次進(jìn)入庫緩存時可能存在重大的啟動延遲。但是聰明的Oracle DBA和開發(fā)人員能夠改變表的搜索限制參數(shù)或者使用ordered提示來手工指定表的連接順序，從而顯著地減少優(yōu)化和執(zhí)行新查詢所需的時間。