日期函数优化SQL查询-优快云博客

为了处理日期类型字段作为条件表达式的规范化，简化SQL收索引擎的设计实现，写了个日期函数f_ec_get_day（），返回带有时间戳的日期型数据。案例语句如下：

select t.intcocode,count(*)
from wzec.Tb_StockOrder t
where t.daSubTime >= to_date('20090710','yyyymmdd')
and t.daSubTime <= to_date('20090715','yyyymmdd')
and t.inttype <=2
and t.strProdCode like '010910%'
group by t.intcocode;

执行计划为

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=21 Card=1 Bytes=30)
   1    0   SORT (GROUP BY) (Cost=21 Card=1 Bytes=30)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TB_STOCKORDER' (Cost=4 Card=1 Bytes=30)
   3    2       INDEX (RANGE SCAN) OF 'IND_TB_STOCKORDER_DASUBTIME' (NON-UNIQUE) (Cost=3 Card=3)

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

替换日期表达式后：

select t.intcocode,count(*)
from wzec.Tb_StockOrder t
where t.daSubTime >= f_ec_get_day('20090710',0)
and t.daSubTime <= f_ec_get_day('20090715',0)
and t.inttype <=2
and t.strProdCode like '010910%'
group by t.intcocode;

执行计划为：

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT ptimizer=CHOOSE (Cost=1937 Card=6 Bytes=180)
   1    0   SORT (GROUP BY) (Cost=1937 Card=6 Bytes=180)
   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'TB_STOCKORDER' (Cost=1920 Card=7 Bytes=210)
   3    2       INDEX (RANGE SCAN) OF 'IND_TB_STOCKORDER_PROD2' (NON-UNIQUE) (Cost=12 Card=2651)

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

很明显，优化器选择了不同的索引，正确的索引选择是第一个语句。原因是什么呢？

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

转载于:http://blog.itpub.net/16496023/viewspace-610766/