下面这样语句可以使用索引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;
;两条语句只差一天,为什么不同强制使用索引?
或者
根据两个日期条件,选出来的记录条数不一样?差很多?