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