ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 优化器的错误选择

优化器的错误选择

原创 Linux操作系统 作者:shaozb 时间:2009-07-29 17:40:04 0 删除 编辑

    为了处理日期类型字段作为条件表达式的规范化,简化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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 奇怪的“%”
下一篇: 优化报告
请登录后发表评论 登录
全部评论

注册时间:2009-07-29

  • 博文量
    8
  • 访问量
    3585