一条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字段的索引。效果提升还行。那就这样了。

PS:如果您想和业内技术大牛交流的话,请加qq群(527933790)或者关注微信公众 号(AskHarries),谢谢!

转载请注明原文出处:Harries Blog™ » 一条sql的改写

赞 (0)

分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址