通過分析SQL語句的執行計劃優化SQL語句

　巢狀迴圈(Nested Loops, NL)

　　這個連線方法有驅動表(外部表)的概念。其實，該連線過程就是一個2層巢狀迴圈，所以外層迴圈的次數越少越好，這也就是我們為什麼將小表或返回較小row source的表作為驅動表(用於外層迴圈)的理論依據。

但是這個理論只是一般指導原則，因為遵循這個理論並不能總保證使語句產生的I/O次數最少。有時不遵守這個理論依據，反而會獲得更好的效率。如果使用這種方法，決定使用哪個表作為驅動表很重要。有時如果驅動表選擇不正確，將會導致語句的效能很差、很差。

　　內部連線過程：

Row source1的Row 1 --------------　　　
-- Probe ->　　　 Row source 2
Row source1的Row 2 --------------　　　
-- Probe ->　　　 Row source 2
Row source1的Row 3 --------------　　　
-- Probe ->　　　 Row source 2
…….
Row source1的Row n --------------　　　
-- Probe ->　　　 Row source 2

　　從內部連線過程來看，需要用row source1中的每一行，去匹配row source2中的所有行，所以此時保持row source1儘可能的小與高效的訪問row source2(一般通過索引實現)是影響這個連線效率的關鍵問題。這只是理論指導原則，目的是使整個連線操作產生最少的物理I/O次數，而且如果遵守這 個原則，一般也會使總的物理I/O數最少。但是如果不遵從這個指導原則，反而能用更少的物理I/O實現連線操作，那儘管違反指導原則吧！因為最少的物理 I/O次數才是我們應該遵從的真正的指導原則，在後面的具體案例分析中就給出這樣的例子。

　　在上面的連線過程中，我們稱Row source1為驅動表或外部表。Row Source2被稱為被探查表或內部表。

　　在NESTED LOOPS連線中，Oracle(大型網站資料庫平臺)讀取 row source1中的每一行，然後在row sourc2中檢查是否有匹配的行，所有被匹配的行都被放到結果集中，然後處理row source1中的下一行。這個過程一直繼續，直到row source1中的所有行都被處理。這是從連線操作中可以得到第一個匹配行的最快的方法之一，這種型別的連線可以用在需要快速響應的語句中，以響應速度為 主要目標。

　　如果driving row source(外部表)比較小，並且在inner row source(內部表)上有唯一索引，或有高選擇性非唯一索引時，使用這種方法可以得到較好的效率。NESTED LOOPS有其它連線方法沒有的的一個優點是：可以先返回已經連線的行，而不必等待所有的連線操作處理完才返回資料，這可以實現快速的響應時間。

　　如果不使用並行操作，最好的驅動表是那些應用了where 限制條件後，可以返回較少行資料的的表，所以大表也可能稱為驅動表，關鍵看限制條件。對於並行查詢，我們經常選擇大表作為驅動表，因為大表可以充分利用並 行功能。當然，有時對查詢使用並行操作並不一定會比查詢不使用並行操作效率高，因為最後可能每個表只有很少的行符合限制條件，而且還要看你的硬體配置是否 可以支援並行(如是否有多個CPU，多個硬碟控制器)，所以要具體問題具體對待。

　　NL連線的例子：

SQL> explain plan for
select a.dname,b.sql
from dept a,emp b
where a.deptno = b.deptno;
Query Plan
-------------------------
SELECT STATEMENT [CHOOSE] Cost=5
NESTED LOOPS
TABLE access(小型網站之最愛) FULL DEPT [ANALYZED]
TABLE access(小型網站之最愛) FULL EMP [ANALYZED]

　　雜湊連線(Hash Join, HJ)

　　這種連線是在Oracle(大型網站資料庫平臺) 7.3以後引入的，從理論上來說比NL與SMJ更高效，而且只用在CBO優化器中。

　　較小的row source被用來構建hash table與bitmap，第2個row source被用來被hansed，並與第一個row source生成的hash table進行匹配，以便進行進一步的連線。Bitmap被用來作為一種比較快的查詢方法，來檢查在hash table中是否有匹配的行。特別的，當hash table比較大而不能全部容納在記憶體中時，這種查詢方法更為有用。這種連線方法也有NL連線中所謂的驅動表的概念，被構建為hash table與bitmap的表為驅動表，當被構建的hash table與bitmap能被容納在記憶體中時，這種連線方式的效率極高。

　　HASH連線的例子：

SQL> explain plan for
select /*+ use_hash(emp) */ empno
from emp, dept
where emp.deptno = dept.deptno;
Query Plan
----------------------------
SELECT STATEMENT　[CHOOSE] Cost=3
　HASH JOIN
　　TABLE access(小型網站之最愛) FULL DEPT
　　TABLE access(小型網站之最愛) FULL EMP

　　要使雜湊連線有效，需要設定HASH_JOIN_ENABLED=TRUE，預設情況下該引數為TRUE，另外，不要忘了還要設定 hash_area_size引數，以使雜湊連線高效執行，因為雜湊連線會在該引數指定大小的記憶體中執行，過小的引數會使雜湊連線的效能比其他連線方式還 要低。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10742223/viewspace-234378/，如需轉載，請註明出處，否則將追究法律責任。