下面这样语句可以使用索引STA_ORDER_ITEM_INDEX2
select /*+INDEX(STA_ORDER_ITEM STA_ORDER_ITEM_INDEX2)*/
a.drug_code, a.drug_name,a.drug_mode,a.drug_spec,nvl(sum(a.money_receive),0) dhje,nvl(sum(a.money_back),0) thje, nvl(sum(a.money_receive),0) - nvl(sum(a.money_back),0) cjje
from sta_order_item a
where STAT_DATE > to_date('2011-10-17','yyyy-MM-dd')
group by a.drug_code, a.drug_name, a.drug_mode, a.drug_spec
having (nvl(sum(a.money_receive),0) +nvl(sum(a.money_back),0))>0 order by nvl(sum(a.money_receive),0) desc;
;下面这条语句不能使用索引
select /*+INDEX(STA_ORDER_ITEM STA_ORDER_ITEM_INDEX2)*/
a.drug_code, a.drug_name,a.drug_mode,a.drug_spec,nvl(sum(a.money_receive),0) dhje,nvl(sum(a.money_back),0) thje, nvl(sum(a.money_receive),0) - nvl(sum(a.money_back),0) cjje
from sta_order_item a
where STAT_DATE > to_date('2011-10-18','yyyy-MM-dd')
group by a.drug_code, a.drug_name, a.drug_mode, a.drug_spec
having (nvl(sum(a.money_receive),0) +nvl(sum(a.money_back),0))>0 order by nvl(sum(a.money_receive),0) desc;
;两条语句只差一天,为什么不同强制使用索引?
select /*+INDEX(STA_ORDER_ITEM STA_ORDER_ITEM_INDEX2)*/
a.drug_code, a.drug_name,a.drug_mode,a.drug_spec,nvl(sum(a.money_receive),0) dhje,nvl(sum(a.money_back),0) thje, nvl(sum(a.money_receive),0) - nvl(sum(a.money_back),0) cjje
from sta_order_item a
where STAT_DATE > to_date('2011-10-17','yyyy-MM-dd')
group by a.drug_code, a.drug_name, a.drug_mode, a.drug_spec
having (nvl(sum(a.money_receive),0) +nvl(sum(a.money_back),0))>0 order by nvl(sum(a.money_receive),0) desc;
;下面这条语句不能使用索引
select /*+INDEX(STA_ORDER_ITEM STA_ORDER_ITEM_INDEX2)*/
a.drug_code, a.drug_name,a.drug_mode,a.drug_spec,nvl(sum(a.money_receive),0) dhje,nvl(sum(a.money_back),0) thje, nvl(sum(a.money_receive),0) - nvl(sum(a.money_back),0) cjje
from sta_order_item a
where STAT_DATE > to_date('2011-10-18','yyyy-MM-dd')
group by a.drug_code, a.drug_name, a.drug_mode, a.drug_spec
having (nvl(sum(a.money_receive),0) +nvl(sum(a.money_back),0))>0 order by nvl(sum(a.money_receive),0) desc;
;两条语句只差一天,为什么不同强制使用索引?
解决方案 »
- 一个sql语句,折磨了我半天.
- 请问oracle 9I 如何备份和恢复
- oracle登录问题?
- 请帮个忙:关于判断数据的递增或者递减
- ORA-06502: PL/SQL: numeric or value error: character string buffer too small
- Ocale临时表问题
- 把Oracle10g下的数据库,导入9i出现错误。
- 妹妹有问题,快来帮忙啊!!急!!
- 请问Oracle是不是不能在P4的机器上安装?我在好几台P4机器上都不行:<
- 启动OracleServiceDATABASE服务出现以下错误,怎办?
- DB2问题:求教sql语句like的用法?
- oracle sequence update 问题...求助
或者
根据两个日期条件,选出来的记录条数不一样?差很多?