案例3　規(guī)范SQL寫法好處多

1.案例說明

某大型電商公司數(shù)據(jù)倉庫系統(tǒng)，開發(fā)人員反映作業(yè)運(yùn)行緩慢。經(jīng)檢查是一個新增業(yè)務(wù)中某條SQL語句導(dǎo)致。經(jīng)分析是非標(biāo)準(zhǔn)的SQL引起優(yōu)化器判斷異常，將其修改成標(biāo)準(zhǔn)寫法后，SQL恢復(fù)正常。

（1）具體分析

看下面的代碼：

select ... from ...
where（
    （   order_creation_date>= to_date（20120208，'yyyy-mm-dd'） and
         order_creation_date<to_date（20120209，'yyyy-mm-dd'）
    ）
or
    （   send_date>= to_date（20120208，'yyyy-mm-dd'） and send_date<to_date（20120209，'yyyy-mm-dd'）
    ））
andnvl（a.bd_id，0） = 1
--------------------------------------------------------------------------------
|  Id | Operation              | Name   |Cost （%CPU）| Time   |Pstart | Pstop |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |        | 2470K（100）|        |       |       |
|   1 |  SORT GROUP BY         |        |           |        |       |       |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID
                                  |  XXXX  |     5 （0） | 00：00：01 | ROW L | ROW L |
|   3 |    NESTED LOOPS         |        | 2470K （1） | 08：14：11 |       |       |
|   4 |     VIEW               |VW_NSO_1| 2470K （1） | 08：14：10 |       |       |
|   5 |      FILTER            |        |           |          |       |       |
|   6 |       HASH GROUP BY    |        |  2470K （1）| 08：14：10 |       |       |
|   7 |        TABLE ACCESS BY GLOBAL INDEX ROWID 
                               |  XXXX  |      5 （0）| 00：00：01 | ROW L | ROW L |
|   8 |         NESTED LOOPS    |        |  2470K （1）| 08：14：10 |       |       |
|   9 |          SORT UNIQUE    |        |  2340K （2）| 07：48：11 |       |       |
|  10 |           PARTITION RANGE ALL  
                                |        |  2340K （2）| 07：48：11 |    1  |    92 |
|  11 |            TABLE ACCESS FULL
                                |  XXXX  |  2340K （2）| 07：48：11 |    1  |    92 |
|  12 |          INDEX RANGE SCAN 
                                |  XXXX  |      3 （0）| 00：00：01 |       |       |
|  13 |     INDEX RANGE SCAN    |  XXXX  |      3 （0）| 00：00：01 |       |       |
--------------------------------------------------------------------------------

這個SQL中涉及的主要表是一個分區(qū)表，從執(zhí)行計(jì)劃（Pstart、Pstop）中可見，掃描了所有分區(qū)，分區(qū)裁剪特性沒有起效。

（2）解決方法

見下面的代碼：

select ...
from ...
where 
    order_creation_date >= to_date（20120208，'yyyy-mm-dd'） and
    order_creation_date<to_date（20120209，'yyyy-mm-dd'）
union all
select ...
from ...
where
send_date>= to_date（20120208，'yyyy-mm-dd'） and
    send_date<to_date（20120209，'yyyy-mm-dd'） and
nvl（a.bd_id，0） = 5

嘗試通過引入union all來分解查詢，以便于優(yōu)化器做出更準(zhǔn)確的判斷。采用這個方法后，確實(shí)起效了，當(dāng)然不可避免會掃描兩遍表。

select ...
from ...
where（
    （
        order_creation_date>= to_date（20120208，'yyyymmdd'） and
        order_creation_date<to_date（20120209，'yyyymmdd'）
    ）
or
    （
        send_date>= to_date（20120208，'yyyymmdd'） and
        send_date<to_date（20120209，'yyyymmdd'）
    ））；
--------------------------------------------------------------------------------
|  Id   | Operation           | Name | Cost（%CPU）|Time      | Pstart  | Pstop   |
--------------------------------------------------------------------------------
|     0 | SELECT STATEMENT    |      |  42358 （1）| 00：08：29 |         |         |
|     1 |  SORT AGGREGATE     |      |           |          |         |         |
|     2 |   CONCATENATION     |      |           |          |         |         |
|     3 |    PARTITION RANGE SINGLE
                              |      |  17393 （1）| 00：03：29 |      57 |     57 |
|*    4 |     TABLE ACCESS FULL
                              | XXXX |  17393 （1）| 00：03：29 |      57 |     57 |
|*    5 |    TABLE ACCESS BY GLOBAL INDEX ROWID 
                              | XXXX |  24966 （1）| 00：05：00 |   ROWID |  ROWID |
|*    6 |     INDEX RANGE SCAN  
                              | XXXX |    658 （1）| 00：00：08 |         |         |
---------------------------------------------------------------------------------

通過調(diào)整日期FORMAT格式，優(yōu)化器很精準(zhǔn)地判斷了分區(qū)（Pstart=57 Pstop=57），整體SQL性能得到了很大的提高。作業(yè)運(yùn)行時間從8個多小時，縮減到8分鐘。

（3）分析結(jié)論

對于非標(biāo)準(zhǔn)的日期格式，Oracle在復(fù)雜邏輯判斷的情況下分區(qū)裁剪特性無法識別，不起作用。這種情況下，會走全表掃描，結(jié)果是正確的，但是執(zhí)行效率會很低。通過使用union all，簡化了條件判斷。使得Oracle在非保準(zhǔn)日期格式下也能使用分區(qū)裁剪特性，但最佳修改方式還是規(guī)范SQL的寫法。

2.給我們的啟示

• 規(guī)范的SQL寫法，不但利于提高代碼可讀性，還有利于優(yōu)化器生成更優(yōu)的執(zhí)行計(jì)劃。
• 分區(qū)功能是Oracle應(yīng)對大數(shù)據(jù)的利器，但在使用中要注意是否真正會用到分區(qū)特性；否則，可能適得其反，使用分區(qū)會導(dǎo)致效率更差。