优化器的错误选择

    为了处理日期类型字段作为条件表达式的规范化，简化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/