转载

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