如下的一条sql:
select o.order_id
from all_order o
where o.gmt_trade_finished>to_date('20070911172907','yyyymmddhh24miss')
and o.gmt_trade_finished<=to_date('20071113160832','yyyymmddhh24miss')
and (mod(to_number(o.order_id),9)=2 and o.site='xxx');
在all_order表中,order_id为varchar2类型,大部分为数字,其中site='xxx'的全部为数字。该表在gmt_trade_finished字段上面有个索引。
就这样的语句在db中是走索引的,除了执行速度有点慢,倒是可以正常执行的。
为了加快执行速度,让它走全表扫描:
select /*+ full(o)*/ o.order_id
from all_order o
where o.gmt_trade_finished>to_date('20070911172907','yyyymmddhh24miss')
and o.gmt_trade_finished<=to_date('20071113160832','yyyymmddhh24miss')
and (mod(to_number(o.order_id),9)=2 and o.site='xxx');
它就会报出
ERROR:
ORA-01722: invalid number
no rows selected
调整where子句后面的顺序:
select /*+ full(o)*/ o.order_id
from all_order o
where o.gmt_trade_finished>to_date('20070911172907','yyyymmddhh24miss')
and o.gmt_trade_finished<=to_date('20071113160832','yyyymmddhh24miss')
and o.site='xxx' and (mod(to_number(o.order_id),9)=2);
就正常。也就是说在加上这个hint后,oracle就按where后面的先后顺序来判断的,在报错的那条语句情况下,oracle会找到含有字符串的记录先来判断mod,然后再来判断site,当然要报错,
调整后,先判断site,再来mod就没问题,因为site='xxx'的order_id全为数字。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/100091/viewspace-983361/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/100091/viewspace-983361/