一条sql的改写

好久没有写博客了，今天在巡检时发现一条sql，走了全表扫描，执行效率很低：

select distinct mcid from pho_delivery where downflg = 0 and (to_date(to_char(sysdate,'yyyymmddhh24miss'),'yyyymmddhh24miss')-to_date(to_char(createdate,'yyyymmddhh24miss'),'yyyymmddhh24miss')) * 24 * 60 BETWEEN 3/60 AND 2

执行计划为：

Plan hash value: 2766889067


-----------------------------------------------------------------------------------
| Id  | Operation   | Name  | Rows  | Bytes | Cost (%CPU)| Time  |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |  |   205 |  3280 | 35375   (1)| 00:07:05 |
|   1 |  HASH UNIQUE   |  |   205 |  3280 | 35375   (1)| 00:07:05 |
|*  2 |   TABLE ACCESS FULL| PHO_DELIVERY |   229 |  3664 | 35374   (1)| 00:07:05 |
-----------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - filter("DOWNFLG"=0 AND (TO_DATE(TO_CHAR(SYSDATE@!,'yyyymmddhh24miss'
     ),'yyyymmddhh24miss')-TO_DATE(TO_CHAR(INTERNAL_FUNCTION("CREATEDATE"),'yyyy
     mmddhh24miss'),'yyyymmddhh24miss'))*24*60>=.05 AND
     (TO_DATE(TO_CHAR(SYSDATE@!,'yyyymmddhh24miss'),'yyyymmddhh24miss')-TO_DATE(
     TO_CHAR(INTERNAL_FUNCTION("CREATEDATE"),'yyyymmddhh24miss'),'yyyymmddhh24mi
     ss'))*24*60<=2)

Statistics
----------------------------------------------------------
 1  recursive calls
 0  db block gets
     129688  consistent gets
 0  physical reads
 0  redo size
333  bytes sent via SQL*Net to client
509  bytes received via SQL*Net from client
 1  SQL*Net roundtrips to/from client
 0  sorts (memory)
 0  sorts (disk)
 0  rows processed

查看此表的索引情况，发现在createdate字段上有索引的存在，但是很明显，这里没有使用到索引，因为对谓词做了to_char，还有计算。看来要改写一下。
分析一下，这是一个between的语句，可改写成>=和<=的情况，然后把sysdate右移，OK，就这么办
经过改写：

select distinct mcid from pho_delivery where downflg = 0 and createdate >= (sysdate-2/1440) and createdate <= (sysdate-0.05/1440)
查询结果跟原来没有变化，查看下执行计划：
 Execution Plan
 ----------------------------------------------------------
 Plan hash value: 850366934
 
 
 -------------------------------------------------------------------------------------------------------
 | Id  | Operation       | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------------------------------
 |   0 | SELECT STATEMENT       |        |     1 |    16 |     5  (20)| 00:00:01 |
 |   1 |  HASH UNIQUE       |        |     1 |    16 |     5  (20)| 00:00:01 |
 |*  2 |   FILTER       |        |       |       |     |       |
 |*  3 |    TABLE ACCESS BY INDEX ROWID| PHO_DELIVERY       |     1 |    16 |     4 (0)| 00:00:01 |
 |*  4 |     INDEX RANGE SCAN       | IDX_DELIVERY_CREADATE |     1 |       |     3 (0)| 00:00:01 |
 -------------------------------------------------------------------------------------------------------
 
 
 Predicate Information (identified by operation id):
 ---------------------------------------------------
 
 
    2 - filter(SYSDATE@!-.00003472222222222222222222222222222222222222>=SYSDATE@!-.0013888888888
        88888888888888888888888888889)
    3 - filter("DOWNFLG"=0)
    4 - access("CREATEDATE">=SYSDATE@!-.001388888888888888888888888888888888888889 AND
        "CREATEDATE"<=SYSDATE@!-.00003472222222222222222222222222222222222222)
  
 Statistics
 ----------------------------------------------------------
    1  recursive calls
    0  db block gets
   66  consistent gets
    0  physical reads
    0  redo size
  590  bytes sent via SQL*Net to client
  520  bytes received via SQL*Net from client
    2  SQL*Net roundtrips to/from client
    0  sorts (memory)
    0  sorts (disk)
    3  rows processed



还是可以的，用到了createdate字段的索引。效果提升还行。那就这样了。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28912313/viewspace-2142005/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28912313/viewspace-2142005/