2012 年，一朋友请求优化下面 SQL，该 SQL 在 RAC 环境中执行，时快时慢，最快时 1 秒，最慢是 3 秒。SQL 语句如下。

    1. SELECT /*+INDEX（TMS,IDX1_TB_EVT_DLV_W）*/
    2.  TMS.MAIL_NUM，
    3.  TMS.DLV_BUREAU_ORG_CODE AS DLVORGCODE，
    4.  RO.ORG_SNAME AS DLVORGNAME，
    5.  TMS.DLV_PSEG_CODE AS DLVSECTIONCODE，
    6.  TMS.DLV_PSEG_NAME AS DLVSECTIONNAME，
    7.  TO_CHAR（TMS.DLV_DATE, 'YYYY-MM-DD HH24:MI:SS'） AS RECTIME，
    8.  TMS.DLV_STAFF_CODE AS HANDOVERUSERCODE，
    9.  TU2.REALNAME AS HANDOVERUSERNAME，
    10.  DECODE（TMS.DLV_STS_CODE， 'I'， '妥投'， 'H'， '未妥投'， TMS.DLV_STS_CODE） AS DLV_STS_CODE，
    11.  CASE
    12.    WHEN TMS.MAIL_NUM LIKE 'EC%' THEN
    13.     '代收'
    14.    WHEN TMS.MAIL_NUM LIKE 'ED%CW' THEN
    15.     '代收'
    16.    WHEN TMS.MAIL_NUM LIKE 'FJ%' THEN
    17.     '代收'
    18.    WHEN TMS.MAIL_NUM LIKE 'GC%' THEN
    19.     '代收'
    20.    ELSE
    21.     '非代收'
    22.  END MAIL_NUM_TYPE
    23.   FROM TB_EVT_DLV_W TMS
    24.   LEFT JOIN RES_ORG RO ON TMS.DLV_BUREAU_ORG_CODE = RO.ORG_CODE
    25.   LEFT JOIN TB_USER TU2 ON TU2.DELVORGCODE = TMS.DLV_BUREAU_ORG_CODE
    26.                        AND TU2.USERNAME = TMS.DLV_STAFF_CODE
    27.  WHERE NOT EXISTS
    28.  （SELECT /*+INDEX(TDW,IDX1_TB_MAIL_SECTION_STORE)*/
    29.          MAIL_NUM
    30.           FROM TB_MAIL_SECTION_STORE TDW
    31.          WHERE TDW.MAIL_NUM = TMS.MAIL_NUM
    32.            AND TDW.DLVORGCODE = TMS.DLV_BUREAU_ORG_CODE
    33.            and TDW.DLVORGCODE = '35000133'
    34.            AND TDW.RECTIME >=
    35.                TO_DATE('2012-11-01 00:00', 'YYYY-MM-DD HH24:MI:SS')
    36.            AND TO_DATE('2012-11-08 15:15', 'YYYY-MM-DD HH24:MI:SS') >=
    37.                TDW.RECTIME
    38.            and rownum = 1）
    39.    AND TMS.DLV_BUREAU_ORG_CODE = '35000133'
    40.    AND TMS.DLV_DATE >= TO_DATE（'2012-11-01 00:00', 'YYYY-MM-DD HH24:MI:SS'）
    41.    AND TO_DATE（'2012-11-08 15:15', 'YYYY-MM-DD HH24:MI:SS'） >= TMS.DLV_DATE
    42.    AND （'' IS NULL OR TMS.DLV_STAFF_CODE = ''）
    43.    AND （'' IS NULL OR TU2.REALNAME LIKE '%%'）
    44.    AND TMS.REC_AVAIL_FLAG = '1'；

    执行计划如下。

    1. Plan hash value: 1159587453
    3. -------------------------------------------------------------------------------------
    4. | Id|Operation                                |Name                      |Rows|Bytes|
    5. -------------------------------------------------------------------------------------
    6. |  0|SELECT STATEMENT                         |                          |    |     |
    7. |* 1| FILTER                                  |                          |    |     |
    8. |  2|  NESTED LOOPS OUTER                     |                          | 131|13493|
    9. |* 3|   HASH JOIN RIGHT OUTER                 |                          | 129|10191|
    10. |* 4|    TABLE ACCESS BY INDEX ROWID          |EMS_USER                  |   6|  120|
    11. |* 5|     INDEX RANGE SCAN                    |EMS_USER_NEW_INX_ORG      |   7|     |
    12. |* 6|    TABLE ACCESS BY GLOBAL INDEX ROWID   |TB_EVT_DLV_W              | 129| 7611|
    13. |* 7|     INDEX RANGE SCAN                    |IDX1_TB_EVT_DLV_W         | 586|     |
    14. |* 8|      COUNT STOPKEY                      |                          |    |     |
    15. |* 9|       FILTER                            |                          |    |     |
    16. | 10|        PARTITION RANGE ITERATOR         |                          |   1|   31|
    17. |*11|        TABLE ACCESS BY LOCAL INDEX ROWID|TB_MAIL_SECTION_STORE     |   1|   31|
    18. |*12|          INDEX RANGE SCAN               |IDX1_TB_MAIL_SECTION_STORE|   1|     |
    19. | 13|   TABLE ACCESS BY INDEX ROWID           |RES_ORG                   |   1|   24|
    20. |*14|    INDEX RANGE SCAN                     |IDX_RES_ORG               |   1|     |
    21. -------------------------------------------------------------------------------------
    23. Predicate Information (identified by operation id):
    24. ---------------------------------------------------
    26.    1 - filter(TO_DATE('2012-11-01 00:00','YYYY-MM-DD HH24:MI:SS')<=TO_DATE('2012-11-08
    27.               15:15','YYYY-MM-DD HH24:MI:SS'))
    28.    3 - access("EU"."USERNAME"="TMS"."DLV_STAFF_CODE" AND 
    29.               "EU"."DELVORGCODE"="TMS"."DLV_BUREAU_ORG_CODE")
    30.    4 - filter("EU"."POSTMANKIND"<>5)
    31.    5 - access("EU"."DELVORGCODE"='35000133')
    32.    6 - filter(("TMS"."DLV_DATE">=TO_DATE('2012-11-01 00:00','YYYY-MM-DD HH24:MI:SS') AND 
    33.               "TMS"."REC_AVAIL_FLAG"='1' AND "TMS"."DLV_DATE"<=TO_DATE('2012-11-08
    34.                15:15','YYYY-MM-DD HH24:MI:SS')))
    35.    7 - access("TMS"."DLV_BUREAU_ORG_CODE"='35000133')  filter( IS NULL)
    36.    8 - filter(ROWNUM=1)
    37.    9 - filter((TO_DATE('2012-11-01 00:00','YYYY-MM-DD HH24:MI:SS')<=TO_DATE('2012-11-08
    38.               15:15','YYYY-MM-DD HH24:MI:SS') AND :B1='35000133'))  
    39.   11 - filter(("TDW"."RECTIME">=TO_DATE('2012-11-01 00:00','YYYY-MM-DD HH24:MI:SS') AND
    40.               "TDW"."RECTIME"<=TO_DATE('2012-11-08 15:15','YYYY-MM-DD HH24:MI:SS')))
    41.   12 - access("TDW"."DLVORGCODE"=:B1 AND "TDW"."MAIL_NUM"=:B2)  
    42.   14 - access("TMS"."DLV_BUREAU_ORG_CODE"="RO"."ORG_CODE")

    首先我们排除执行计划中 Id=8 到 Id=12 会影响 SQL 性能的可能性，因为 Id=8 到 Id=12 只返回 1 行（Id=8，COUNT STOPKEY，ROWNUM=1）数据，返回 1 行数据不可能产生性能问题。执行计划的入口是 Id=5，走的是索引范围扫描，过滤条件是「EU」.「DELVORGCODE」=’35000133’，Id=4 是 Id=5 中索引范围扫描的回表操作，在回表的时候还进行了过滤「EU」.「POSTMANKIND」<>5。如果要追求完美，我们可以将 POSTMANKIND 列放到 Id=5 中的索引中，创建组合索引。Id=5 返回的数据量较少，因此排除了 Id=5 和 Id=4 产生性能问题的可能性。现在我们将目光转移到 Id=7 和 Id=6 上面来。Id=7 走的是索引范围扫描，过滤条件是「TMS」.「DLV_BUREAU_ORG_CODE」=’35000133’，Id=6 是 Id=7 的索引回表操作，注意 Id=6，Operation 中出现了 GLOBAL 关键字，这说明 TB_EVT_DLV_W 是一个分区表，而且 Id=7 中的索引是全局索引。Id=6 中出现了时间过滤，一般的分区表都是根据时间字段进行分区的。于是我们询问朋友 TB_EVT_DLV_W 是不是根据 DLV_DATE 进行分区的，朋友回答是。得到朋友的肯定回答，我们就知道该 SQL 的性能问题出在何处了，问题出在 Id=7 和 Id=6 上。

    我们应该将 Id=7 的全局（global）索引改成本地（local）索引。

    1. create index IDX2_TB_EVT_DLV_W on TB_EVT_DLV_W(DLV_BUREAU_ORG_CODE) local;

    改成本地索引之后，Id=6 就不会再去进行时间过滤了。相比扫描全局索引，扫描本地索引只需要到对应的索引分区中进行扫描，扫描的叶子块数量也大大减少。建立本地索引之后，SQL 多次执行都能稳定在 1 秒内。

    如果过滤条件中有分区字段，一般都创建本地索引。

    如果过滤条件中没有分区字段，一般都创建 global 索引，如果这时创建成 local 索引，会扫描所有的索引分区，分区数量越多，性能下降越明显。假设有 1 000 个分区，在进行索引扫描的时候会扫描 1 000 个索引分区，此时相比 global 索引，会额外多读取至少 1 000 个索引块。

    假设表按月分区，一个月大概几百万行数据，但是只查询几小时的数据，数据也就几千行，这时我们需要将分区列包含在索引中，这样的索引就是有前缀的本地索引。假设表按月分区，但是查询经常按月查询或者跨月查询，这时我们就不需要将分区列包含在索引中，这样创建的本地索引就是非前缀的本地索引。