解析Oracle資料掃描 Oracle SQL查詢最佳化 引導局部範圍資料掃描的方法（6）

通過分離SQL語句實現局部範圍資料掃描

        通常情況下應該盡量使實現某個功能的SQL語句的各個組成部分放在一起運行比較有效。但是在少數的情況下，使用將SQL分離的方法反倒比較有效。這種情況主要存在於，經常對具有1:M關係的兩張表進行查詢時，起到對“M”方過濾條件的資料來自於”1”方。通常“M”方的資料會比較多的可能性較大，如果此時為“1”方的表賦予了一個對應資料範圍較大的查詢條件，則在整個資料處理過程中，就需要付出較大的代價。如下面的語句樣本：

Select y.deptno,y.ename,y.empno,y.job,x.sal_tot,x.comm_tot

From(select empno,sum(sal_amt) sal_tot,sum(comm) comm_tot

     From salary s

     Where s.deptno like ‘12%’

     And s.sal_date between ‘20100101’ and ‘20101231’

     Group by empno) x,employee y

Where y.empno=x.empno;

        該語句實現查詢部門編號以“12”開頭的部門中每個員工的年工資的總和。這裡為了提升查詢效能，將deptno欄位冗餘進了salary表中，同時在salary表上構建了基於deptno和sal_date欄位的複合索引（inx_dept_sal），並且我們可以知道employee表和salary表是典型的1:M關係。

該語句的驅動查詢條件是s.deptno like ‘12%’，此時無法使用局部範圍掃描，而且通過該條件掃描資料量很大的salary表，需要讀取很多部門的全部員工的工資資訊，可想而知代價甚大。不僅如此，儘管查詢時使用了salary表上的複合索引inx_dept_sal，但是由於針對複合索引的首欄位沒有使用等值條件，所以根據複合式索引的基本原理，可以知道索引掃描的資料量也會非常大。

        通過分析上述語句的執行邏輯，我們可以看出該語句的驅動查詢條件其實是來自於employee表的deptno欄位，而employee表的資料量相對於salary表來說相對較小。因此如果分離原來的SQL語句，首先按照驅動條件掃描employee表獲得需要的deptno資訊，然後使用這些deptno作為過濾條件來過濾salary表進而擷取最終的查詢結果就可能獲得較好的查詢效能。因為首先可以利用從employee表中擷取的deptno使用等值（=）條件來過濾salary表；其次我們可以控制每次按照驅動查詢條件檢索employee表所返回的資料範圍，這樣便可以變相的實現了局部範圍掃描。因此我們可以如下所示分離原來的SQL語句：

Œ     按照驅動查詢條件單獨掃描employee表：

Select deptno into :v_deptno from employee like ‘12%’;

此時如果employee表的deptno欄位如果存在合適的索引，那麼掃描效能會更好，因而整體的查詢效能會更高。

      通過獲得deptno資訊來過濾salary表的到最終結果：

Select y.deptno,y.ename,y.empno,y.job,x.sal_tot,x.comm_tot

From(select empno,sum(sal_amt) sal_tot,sum(comm) comm_tot

    From salary s

     Where s.deptno=:v_deptno

         And s.sal_date between ‘20100101’ and ‘20101231’

         Group by empno) x,employee y

Where y.empno=x.empno;

      此時對salary表的驅動查詢條件變成等值條件，因此可以充分發揮複合索引inx_dept_sal的作用實現效能的提升。

這種分離SQL語句的方法，需要注意以下三點：

第一、    要準確的識別出應用場合，通常的應用場合是，對具有1:M關係的兩張表進行查詢時，起到對“M”方過濾條件的資料來自於”1”方，同時“M”方的資料量較大，並且語句需要對“M”進行耗時操作（如：分組統計或排序），同時“1”方的資料量相對較小；

第二、    要在重要的過濾欄位上構建合適的索引，以便最大幅度提升局部的查詢效能；

第三、    要對被分離出的首先需要執行的資料掃描進行控制，使之分批返回用於過濾的重要欄位資訊（如果資料範圍較大時），以便實現局部範圍掃描；

        可見這種方法需要我們在設計上進行更多的思考，同時在實現上也需要更多的附加步驟以及更多的控制性行為（如：分批返回首先進行的小資料量表的掃描結果的手段），但是對比我們所能獲得效能提升，這些代價也是值得的。但不管怎麼說這種方法都是需要進行代價評估的，因此它的適用範圍是有限的，在使用時一定要把住上述的3個注意點來進行合理的設計。

       如果不想分離原來的SQL語句，那麼此種情形下想提升效能的方法就非常有限了，但是也還是有方法的，此時就需要使用古老的基於規則的最佳化器了，同時需要在關鍵過濾欄位上存在合理高效的索引。此時可以如下面的方式來實現原來的語句：

Select/*+ rule */ y.deptno,y.ename,y.empno,y.job,x.sal_tot,x.comm_tot

From(select empno,sum(sal_amt) sal_tot,sum(comm) comm_tot

From salary s

Where s.deptno in (Select deptno from employee like ‘12%’)

  And s.sal_date between ‘20100101’ and ‘20101231’

  Group by empno) x,employee y

Where y.empno=x.empno;

通過使用rule提示，來使得SQL語句按照索引規則執行，首先執行Select deptno from employee like ‘12%’，擷取通過s.deptno欄位進行索引過濾的所需的資訊。還可以如下方式改寫：

Select y.deptno,y.ename,y.empno,y.job,x.sal_tot,x.comm_tot

From(select/*+ index(s inx_dept_sal) */

 empno,sum(sal_amt) sal_tot,sum(comm) comm_tot

From salary s

Where s.deptno in(Select deptno from employee like ‘12%’)

  And s.sal_date between ‘20100101’ and ‘20101231’

  Group by empno) x,employee y

Where y.empno=x.empno;

        兩種改寫的目的都是保證首先執行小資料量的掃描，進而通過利用索引實現對大資料量表的資料過濾。但是後兩種的改寫是否會在執行過程中生效，這依賴於不同版本的Oracle執行最佳化器，因此需要進行確認測試，同時後兩種改寫也不一定會確保進行局部範圍資料掃描，但無論如何第一種方式無疑是有效。