2017 年，网络优化班的学生问怎么优化以下语句。

    1. select rownum as r, a.*
    2.   from (select npai.AREA_ID,
    3.                npai.PSO_ID,
    4.                npai.RO_ID,
    5.                npai.NO,
    6.                npai.ADDR,
    7.                to_char(npai.CRTD_DT, 'yyyy-mm-dd hH24:mi:ss') as CRTD_DT,
    8.                to_char(npai.CMPLT_DT, 'yyyy-mm-dd hH24:mi:ss') as CMPLT_DT,
    9.                npai.CRM_PROD_ID,
    10.                npai.PROD_SERV_SPEC_ID,
    11.                npai.PROD_SERV_SPEC_NAME,
    12.                npai.ACTION_TP_ID,
    13.                npai.ACTION_TP_NAME
    14.           from NT_PSO_ARCH_INFO npai
    15.          where npai.crtd_dt >= to_date('2017-01-01', 'yyyy-mm-dd')
    16.            and npai.crtd_dt <= to_date('2017-02-01', 'yyyy-mm-dd')
    17.            and local_area_id = 3
    18.          order by crtd_dt) a
    19.  where rownum <= 20;

    执行计划如下。

    1. Plan hash value: 2467293374
    3. -------------------------------------------------------------------------------------
    4. | Id |Operation                             |Name            |Rows |Bytes|Cost(%CPU)|
    5. -------------------------------------------------------------------------------------
    6. |  0 |SELECT STATEMENT                      |                |   20|28160|  489K (1)|
    7. |* 1 | COUNT STOPKEY                        |                |     |     |          |
    8. |  2 |  VIEW                                |                | 950K|1276M|  489K (1)|
    9. |* 3 |   SORT ORDER BY STOPKEY              |                | 950K|  85M|  489K (1)|
    10. |  4 |    PARTITION LIST SINGLE             |                | 950K|  85M|  469K (1)|
    11. |  5 |     TABLE ACCESS BY LOCAL INDEX ROWID|NT_PSO_ARCH_INFO| 950K|  85M|  469K (1)|
    12. |* 6 |      INDEX RANGE SCAN                |IDX_NTPAI_CRDT  | 950K|     | 2581  (1)|
    13. -------------------------------------------------------------------------------------
    15. Predicate Information (identified by operation id):
    16. ---------------------------------------------------
    18.    1 - filter(ROWNUM<=20)
    19.    3 - filter(ROWNUM<=20)
    20.    6 - access("NPAI"."CRTD_DT">=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 
    21.               AND "NPAI"."CRTD_DT"<=TO_DATE('2017-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

    该 SQL 类似分页语句。拿到分页语句，我们应该先查看分页语句是否符合分页编码规范。这里，SQL 完全符合分页语句编码规范。

    该 SQL 排序列是 crtd_dt，执行计划中走的也是 crtd_dt 列的索引。表 nt_pso_arch_info 是 LIST 分区表，分区列是 local_area_id，从执行计划中（Id=4）看到只扫描了一个分区。按道理该 SQL 不应该出现 SORT ORDER BY。为什么执行计划中有 SORT ORDER BY 呢？我们注意观察，SQL 语句中 order by 的列 crtd_dt 在 select 中进行了 to_char 格式化，格式化之后取了别名，但是别名居然与列名一样。正是因为别名与列名一样，才导致无法消除 SORT ORDER BY。

    现在我们另外取一个别名（CRTD_DT1）。

    1. select rownum as r, a.*
    2.   from (select npai.AREA_ID,
    3.                npai.PSO_ID,
    4.                npai.RO_ID,
    5.                npai.NO,
    6.                npai.ADDR,
    7.                to_char(npai.CRTD_DT, 'yyyy-mm-dd hH24:mi:ss') as CRTD_DT1,
    8.                to_char(npai.CMPLT_DT, 'yyyy-mm-dd hH24:mi:ss') as CMPLT_DT,
    9.                npai.CRM_PROD_ID,
    10.                npai.PROD_SERV_SPEC_ID,
    11.                npai.PROD_SERV_SPEC_NAME,
    12.                npai.ACTION_TP_ID,
    13.                npai.ACTION_TP_NAME
    14.           from NT_PSO_ARCH_INFO npai
    15.          where npai.crtd_dt >= to_date('2017-01-01', 'yyyy-mm-dd')
    16.            and npai.crtd_dt <= to_date('2017-02-01', 'yyyy-mm-dd')
    17.            and local_area_id = 3
    18.          order by crtd_dt) a
    19.  where rownum <= 20;

    我们再次查看执行计划。

    1. Plan hash value: 3066843972
    3. -------------------------------------------------------------------------------------
    4. | Id |Operation                            |Name            |Rows |Bytes| Cost(%CPU)|
    5. -------------------------------------------------------------------------------------
    6. |  0 |SELECT STATEMENT                     |                |   20|28160|   489K (1)|
    7. |* 1 | COUNT STOPKEY                       |                |     |     |           |
    8. |  2 |  VIEW                               |                | 950K|1276M|   489K (1)|
    9. |  3 |   PARTITION LIST SINGLE             |                | 950K|  85M|   469K (1)|
    10. |  4 |    TABLE ACCESS BY LOCAL INDEX ROWID|NT_PSO_ARCH_INFO| 950K|  85M|   469K (1)|
    11. |* 5 |     INDEX RANGE SCAN                |IDX_NTPAI_CRDT    950K|     |  2581  (1)|
    12. -------------------------------------------------------------------------------------
    14. Predicate Information (identified by operation id):
    15. ---------------------------------------------------
    17.     1 - filter(ROWNUM<=20)
    18.     5 - access("NPAI"."CRTD_DT">=TO_DATE(' 2017-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND 
    19.           "NPAI"."CRTD_DT"<=TO_DATE(' 2017-02-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

    更改别名之后，消除了 SORT ORDER BY，从而达到了优化目的。为什么必须要更改别名呢？这是因为如果不更改别名，order by crtd_dt 就相当于 order by 别名，也就是 order by to_char　（npai.CRTD_DT, ‘yyyy-mm-dd hH24:mi:ss’），而索引中记录的是 date 类型，现在排序变成了按照 char 类型排序，如果不更改别名执行计划就无法消除 SORT ORDER BY。