sql最佳化(查詢條件的變化對執行計畫的影響)

    今天在工作中統計一個資料，最開始比較順利的執行完了sql，但後來使用者稍稍調整了需求，本來我以為增加一個查詢條件就可以搞定的事情，結果執行了二十多分鐘才出結果。後來我查看了下執行計畫，發現前後兩個sql的執行計畫有變化。

    這兩個SQL分別為：

SELECT T.YEARID, SUM(T.EXPORTSUM)  FROM STDW.F_CUSTOM_EXPORTDETAIL T,       (SELECT A.ZONE, A.ZONE_NAME          FROM STDW.D_CUSTOM_PROVINCE_ZONE   A,               STDW.D_CUSTOM_BRANCH_PROVINCE B         WHERE A.PROVINCE_NO = B.PROVICEID           AND B.CORPID = 4400) T2 WHERE T.CITYNO = T2.ZONE   AND (T.CUSTOMCODE8 LIKE '03061%' OR T.CUSTOMCODE8 LIKE '03%' OR       (T.CUSTOMCODE8 LIKE '16%' AND T.CUSTOMCODE8 NOT LIKE '1601%' AND       T.CUSTOMCODE8 NOT LIKE '1602%'))   AND T.YEARID BETWEEN 2010 AND 2012 GROUP BY T.YEARID

SELECT T.YEARID, SUM(T.EXPORTSUM)  FROM STDW.F_CUSTOM_EXPORTDETAIL T,       (SELECT A.ZONE, A.ZONE_NAME          FROM STDW.D_CUSTOM_PROVINCE_ZONE   A,               STDW.D_CUSTOM_BRANCH_PROVINCE B         WHERE A.PROVINCE_NO = B.PROVICEID           AND B.CORPID = 4400           AND B.PROVICEID = 44) T2 WHERE T.CITYNO = T2.ZONE   AND (T.CUSTOMCODE8 LIKE '03061%' OR T.CUSTOMCODE8 LIKE '03%' OR       (T.CUSTOMCODE8 LIKE '16%' AND T.CUSTOMCODE8 NOT LIKE '1601%' AND       T.CUSTOMCODE8 NOT LIKE '1602%'))   AND T.YEARID BETWEEN 2010 AND 2012 GROUP BY T.YEARID

    第一個SQL的執行計畫為：

    第二個SQL執行計畫為：

    這裡需要說明一下背景，表F_CUSTOM_EXPORTDETAIL是一個按年進行分區的表，每個分區下都是千萬層級以上的資料量，差不多都是三、四千萬的資料量。對於表D_CUSTOM_BRANCH_PROVINCE，CORPID欄位為4400的資料有兩條，PROVICEID欄位為44的資料只有一條。

    在第一次只限制CORPID=4400時，SQL執行時間差不多2分鐘（畢竟表F_CUSTOM_EXPORTDETAIL較大），看執行計畫還比較合理。但在第二次再增加限制條件"B.PROVICEID = 44"後，SQL執行時間明顯變長，差不多要20多分鐘。通過分析這兩個SQL的執行計畫，發現第二條SQL的執行計畫有變化：表的串連方式變成了NESTED LOOPS。如果表資料量大的時候，這種方式是比較耗時的。

    之前遇到的情況是相同的SQL，相隔一段時間後執行計畫發生了變化，原因在於統計資訊沒有及時收集，解決方案是利用了oralce的hint。但這次和之前的情況不太一樣，畢竟SQL不太一樣，且這兩條SQL的執行計畫都是固定的。為了摸清根源，這次沒有利用hint，而是去分析了第二條SQL。

    前面也提到，增加了條件"B.PROVICEID = 44"後，D_CUSTOM_BRANCH_PROVINCE只有一條記錄，這時ORACLE會自作聰明的進行使用NESTED LOOPS表串連方式，實際上也是有道理的，但是因為最後關聯的表F_CUSTOM_EXPORTDETAIL資料量過大，才導致執行效率低下。在這種情況下比較好的辦法是使用HASH JOIN表串連方式，怎麼樣才能做到呢？根據前面的分析，我們應該首先考慮到破除只有一條記錄這種可能。分析第二條SQL裡面的子查詢：

SELECT A.ZONE, A.ZONE_NAME          FROM STDW.D_CUSTOM_PROVINCE_ZONE   A,               STDW.D_CUSTOM_BRANCH_PROVINCE B         WHERE A.PROVINCE_NO = B.PROVICEID           AND B.CORPID = 4400           AND B.PROVICEID = 44

    由於D_CUSTOM_BRANCH_PROVINCE限制條件後只有一條記錄，且D_CUSTOM_PROVINCE_ZONE和該表進行關聯時用到了PROVICEID，那我們可以不用D_CUSTOM_BRANCH_PROVINCE，直接限制表D_CUSTOM_PROVINCE_ZONE的條件，如下：

SELECT A.ZONE, A.ZONE_NAME          FROM STDW.D_CUSTOM_PROVINCE_ZONE A         WHERE A.PROVINCE_NO = 44

    這下就完全和表D_CUSTOM_BRANCH_PROVINCE脫離了干係，但實際效果是一樣的。

    最後SQL修改為：

SELECT T.YEARID 年份, SUM(T.EXPORTSUM) 出口金額  FROM STDW.F_CUSTOM_EXPORTDETAIL T,       (SELECT A.ZONE, A.ZONE_NAME          FROM STDW.D_CUSTOM_PROVINCE_ZONE A         WHERE A.PROVINCE_NO = 44) T2 WHERE T.CITYNO = T2.ZONE   AND (T.CUSTOMCODE8 LIKE '03061%' OR T.CUSTOMCODE8 LIKE '03%' OR       (T.CUSTOMCODE8 LIKE '16%' AND T.CUSTOMCODE8 NOT LIKE '1601%' AND       T.CUSTOMCODE8 NOT LIKE '1602%'))   AND T.YEARID BETWEEN 2010 AND 2012 GROUP BY T.YEARID

    執行計畫為：

    這下執行SQL又只要2分鐘左右了。

    總結：

    1、理論上來說可選擇性越高，SQL執行效率越高，但也要分情況：如果查詢的結果只有一條了，可能會影響到表之間的關聯方式，導致執行效率低下。

    2、查看執行計畫時要多注意表之間的串連方式，而且要多想想ORACLE為什麼要採用此方式，這樣有助於解決問題。

    3、通過執行計畫可以看出，在訪問detail表時，如果不走分區，效率會非常低。因此在最佳化時，應盡量讓分區表走分區。