要下班的时候，网友发来QQ消息，说SQL跑得慢，要1-3秒出结果，希望优化一下：

SELECT   /*+INDEX(TMS,IDX1_TB_EVT_DLV_W)*/
TMS.MAIL_NUM,
		TMS.DLV_BUREAU_ORG_CODE AS DLVORGCODE,
		RO.ORG_SNAME AS DLVORGNAME,
		TMS.DLV_PSEG_CODE AS DLVSECTIONCODE,
		TMS.DLV_PSEG_NAME AS DLVSECTIONNAME,
		TO_CHAR(TMS.DLV_DATE,'YYYY-MM-DD HH24:MI:SS') AS RECTIME,
		TMS.DLV_STAFF_CODE AS HANDOVERUSERCODE,
		TU2.REALNAME AS  HANDOVERUSERNAME,
		DECODE( TMS.DLV_STS_CODE ,'I','妥投','H','未妥投', TMS.DLV_STS_CODE) AS  DLV_STS_CODE,
     CASE WHEN  TMS.MAIL_NUM LIKE 'EC%' THEN '代收'
            WHEN TMS.MAIL_NUM LIKE 'ED%CW' THEN '代收'
            WHEN  TMS.MAIL_NUM LIKE 'FJ%' THEN '代收'
            WHEN  TMS.MAIL_NUM LIKE 'GC%' THEN '代收'
               ELSE
                           '非代收'
                        END MAIL_NUM_TYPE 
		FROM TB_EVT_DLV_W TMS
		LEFT JOIN RES_ORG RO ON TMS.DLV_BUREAU_ORG_CODE=RO.ORG_CODE
    LEFT JOIN TB_USER TU2 ON TU2.DELVORGCODE=TMS.DLV_BUREAU_ORG_CODE AND TU2.USERNAME=TMS.DLV_STAFF_CODE
		WHERE NOT EXISTS (SELECT  /*+INDEX(TDW,IDX1_TB_MAIL_SECTION_STORE)*/ MAIL_NUM FROM TB_MAIL_SECTION_STORE TDW WHERE
			 	 TDW.MAIL_NUM = TMS.MAIL_NUM
              AND TDW.DLVORGCODE = TMS.DLV_BUREAU_ORG_CODE
              and  TDW.DLVORGCODE='35000133'
                AND TDW.RECTIME >= TO_DATE('2012-11-01 00:00','YYYY-MM-DD HH24:MI:SS')
                AND TO_DATE('2012-11-08 15:15','YYYY-MM-DD HH24:MI:SS') >= TDW.RECTIME  and rownum=1   )
                AND 
               TMS.DLV_BUREAU_ORG_CODE = '35000133'
                AND TMS.DLV_DATE >=     TO_DATE('2012-11-01 00:00','YYYY-MM-DD HH24:MI:SS')
                AND TO_DATE('2012-11-08 15:15','YYYY-MM-DD HH24:MI:SS') >= TMS.DLV_DATE
                AND ('' IS NULL OR TMS.DLV_STAFF_CODE ='')
                AND ('' IS NULL OR TU2.REALNAME  LIKE '%%')
                AND TMS.REC_AVAIL_FLAG = '1' 

Plan hash value: 1159587453
 
----------------------------------------------------------------
| Id  | Operation                                 | Name                       | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------
|   0 | SELECT STATEMENT                          |                            |       |       |   322K(100)|          |       |       |
|*  1 |  FILTER                                   |                            |       |       |            |          |       |       |
|   2 |   NESTED LOOPS OUTER                      |                            |   131 | 13493 |   928   (1)| 00:00:12 |       |       |
|*  3 |    HASH JOIN RIGHT OUTER                  |                            |   129 | 10191 |   670   (1)| 00:00:09 |       |       |
|*  4 |     TABLE ACCESS BY INDEX ROWID           | EMS_USER                   |     6 |   120 |     8   (0)| 00:00:01 |       |       |
|*  5 |      INDEX RANGE SCAN                     | EMS_USER_NEW_INX_ORG       |     7 |       |     3   (0)| 00:00:01 |       |       |
|*  6 |     TABLE ACCESS BY GLOBAL INDEX ROWID    | TB_EVT_DLV_W               |   129 |  7611 |   661   (0)| 00:00:08 | ROWID | ROWID |
|*  7 |      INDEX RANGE SCAN                     | IDX1_TB_EVT_DLV_W          |   586 |       |    86   (0)| 00:00:02 |       |       |
|*  8 |       COUNT STOPKEY                       |                            |       |       |            |          |       |       |
|*  9 |        FILTER                             |                            |       |       |            |          |       |       |
|  10 |         PARTITION RANGE ITERATOR          |                            |     1 |    31 |   246   (0)| 00:00:03 |   KEY |   KEY |
|* 11 |          TABLE ACCESS BY LOCAL INDEX ROWID| TB_MAIL_SECTION_STORE      |     1 |    31 |   246   (0)| 00:00:03 |   KEY |   KEY |
|* 12 |           INDEX RANGE SCAN                | IDX1_TB_MAIL_SECTION_STORE |     1 |       |   245   (0)| 00:00:03 |   KEY |   KEY |
|  13 |    TABLE ACCESS BY IND