SQL性能調優--去除索引函數修改時間判斷邏輯

前段時間遇到一條可以調優的SQL，這裡記錄一下，梳理知識點，在這裡也做個分享，立個Flag。

結論：

如下這條SQL每次執行都會Full Table Scan TAB1012，檢視該SQL的執行計劃，建議更具查詢條件修改SQL，拿掉Index欄位的TRUNC()函數，且修改時間判斷邏輯，可走Index掃描查詢，時間從25.7秒下降到0.5秒，Cost也可從38214下降到896。

SQL如下：

SELECT A.TFC_CODE,A.PLAN_N_TFC_CODE,A.BOOK_NO,
B.FINISH_DATE,A.PLAN_TRANS_PORT,B.BKG_PLACE||B.BKG_BRANCH_CODE BKG_OFFICE 
FROM TAB1013 A,TAB1012 B 
WHERE A.BOOK_NO=B.BOOK_NO 
AND B.BKG_PLACE||B.BKG_BRANCH_CODE = 'SGSIN01' 
AND B.FINISH_DATE IS NOT NULL 
AND A.PLAN_N_TFC_CODE IS NOT NULL 
AND TRUNC(SYSDATE)-TRUNC(B.FINISH_DATE)>=1 AND TRUNC(SYSDATE)-TRUNC(B.FINISH_DATE)<=7;
           

因為有對Index欄位使用TRUNC函數，導緻Full Table Scan。

如下該SQL的執行計劃：

Execution Plan
----------------------------------------------------------
Plan hash value: 3572234384
-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |    53 |  2968 | 38214   (1)| 00:07:39 |
|   1 |  NESTED LOOPS                |            |    53 |  2968 | 38214   (1)| 00:07:39 |
|   2 |   NESTED LOOPS               |            |    53 |  2968 | 38214   (1)| 00:07:39 |
|*  3 |    TABLE ACCESS FULL         | TAB1012    |    53 |  1537 | 38203   (1)| 00:07:39 |
|*  4 |    INDEX UNIQUE SCAN         | TAB1013_PK |     1 |       |     1   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| TAB1013    |     1 |    27 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
        849  recursive calls
          0  db block gets
     174841  consistent gets
     173756  physical reads
          0  redo size
        349  bytes sent via SQL*Net to client
        356  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
        102  sorts (memory)
          0  sorts (disk)
          0  rows processed

           

看該Table結構，發現該Table的組合Index字段是（FINISH_DATE, 1），如下圖：

但是SQL對Index欄位調用TRUNC函數導緻查詢無法走索引掃描，通常如果有可替代方案則最好是不要對Index欄位使用函數，這裡我們可以對該SQL拿掉TRUNC函數，再修改時間判斷方法，則查詢時走索引掃描，提高SQL性能。

即修改成如下：

AND B.FINISH_DATE<=TRUNC(SYSDATE) AND B.FINISH_DATE>=TRUNC(SYSDATE)-7
           

則走Index掃描查詢，如下該SQL的執行計劃：

Execution Plan
----------------------------------------------------------
Plan hash value: 1230931816
------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |               |   127 |  7112 |   896   (0)| 00:00:11 |
|*  1 |  FILTER                        |               |       |       |            |          |
|   2 |   NESTED LOOPS                 |               |   127 |  7112 |   896   (0)| 00:00:11 |
|   3 |    NESTED LOOPS                |               |   127 |  7112 |   896   (0)| 00:00:11 |
|*  4 |     TABLE ACCESS BY INDEX ROWID| TAB1012       |   127 |  3683 |   871   (1)| 00:00:11 |
|*  5 |      INDEX RANGE SCAN          | TAB1012_IDX_3 | 12671 |       |    12   (0)| 00:00:01 |
|*  6 |     INDEX UNIQUE SCAN          | TAB1013_PK    |     1 |       |     1   (0)| 00:00:01 |
|*  7 |    TABLE ACCESS BY INDEX ROWID | TAB1013       |     1 |    27 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
        584  recursive calls
          0  db block gets
       5215  consistent gets
       2681  physical reads
        312  redo size
        358  bytes sent via SQL*Net to client
        356  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
         13  sorts (memory)
          0  sorts (disk)
          0  rows processed