案例3　规范SQL写法好处多

1.案例说明

某大型电商公司数据仓库系统，开发人员反映作业运行缓慢。经检查是一个新增业务中某条SQL语句导致。经分析是非标准的SQL引起优化器判断异常，将其修改成标准写法后，SQL恢复正常。

（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中涉及的主要表是一个分区表，从执行计划（Pstart、Pstop）中可见，扫描了所有分区，分区裁剪特性没有起效。

（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来分解查询，以便于优化器做出更准确的判断。采用这个方法后，确实起效了，当然不可避免会扫描两遍表。

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 |         |         |
---------------------------------------------------------------------------------

通过调整日期FORMAT格式，优化器很精准地判断了分区（Pstart=57 Pstop=57），整体SQL性能得到了很大的提高。作业运行时间从8个多小时，缩减到8分钟。

（3）分析结论

对于非标准的日期格式，Oracle在复杂逻辑判断的情况下分区裁剪特性无法识别，不起作用。这种情况下，会走全表扫描，结果是正确的，但是执行效率会很低。通过使用union all，简化了条件判断。使得Oracle在非保准日期格式下也能使用分区裁剪特性，但最佳修改方式还是规范SQL的写法。

2.给我们的启示

• 规范的SQL写法，不但利于提高代码可读性，还有利于优化器生成更优的执行计划。
• 分区功能是Oracle应对大数据的利器，但在使用中要注意是否真正会用到分区特性；否则，可能适得其反，使用分区会导致效率更差。