SQL:
select fexp_bill_no, bill_type, fexp_re
from (select fexp_bill_no,
case
when exp.FEXP_FORWARD_FLAG = 'N' then exp.fexp_rp_flag
when exp.FEXP_FORWARD_FLAG = 'Y' and exp.fexp_rp_flag = '0' then '5'
when exp.FEXP_FORWARD_FLAG = 'Y' and exp.fexp_rp_flag = '1' then '6'
end bill_type,
fexp_re,
row_number() over(partition by exp.fexp_bill_no order by exp.fexp_charge_id, exp.fexp_currency_code) rn
from fexpense exp
where exp.fexp_cancel_flag = 'N' and
exp.fexp_re is not null)
where rn = 1fexpense表的数据为638721条,查出记录数为2261条,执行时间为2.641s,大家有什么好的优化方法(建立了对fexp_bill_no,fexp_charge_id,fexp_currency_code的联合索引,更慢,要7S多)
select fexp_bill_no, bill_type, fexp_re
from (select fexp_bill_no,
case
when exp.FEXP_FORWARD_FLAG = 'N' then exp.fexp_rp_flag
when exp.FEXP_FORWARD_FLAG = 'Y' and exp.fexp_rp_flag = '0' then '5'
when exp.FEXP_FORWARD_FLAG = 'Y' and exp.fexp_rp_flag = '1' then '6'
end bill_type,
fexp_re,
row_number() over(partition by exp.fexp_bill_no order by exp.fexp_charge_id, exp.fexp_currency_code) rn
from fexpense exp
where exp.fexp_cancel_flag = 'N' and
exp.fexp_re is not null)
where rn = 1fexpense表的数据为638721条,查出记录数为2261条,执行时间为2.641s,大家有什么好的优化方法(建立了对fexp_bill_no,fexp_charge_id,fexp_currency_code的联合索引,更慢,要7S多)
select fexp_bill_no, bill_type, fexp_re
from
(
select fexp_bill_no,
case
when exp.FEXP_FORWARD_FLAG = 'N' then exp.fexp_rp_flag
when exp.FEXP_FORWARD_FLAG = 'Y' and exp.fexp_rp_flag = '0' then '5'
when exp.FEXP_FORWARD_FLAG = 'Y' and exp.fexp_rp_flag = '1' then '6'
end bill_type,
fexp_re
from fexpense exp
where exp.fexp_cancel_flag = 'N' and
exp.fexp_re is not null and
rowid in
(select rowid from(select rowid from fexpense t group by t.fexp_bill_no order by t.fexp_charge_id,t.fexp_currency_code ) where rownum<=1)
==========================
select fexp_bill_no,
case
when exp.FEXP_FORWARD_FLAG = 'N' then exp.fexp_rp_flag
when exp.FEXP_FORWARD_FLAG = 'Y' and exp.fexp_rp_flag = '0' then '5'
when exp.FEXP_FORWARD_FLAG = 'Y' and exp.fexp_rp_flag = '1' then '6'
end bill_type,
fexp_re
from fexpense exp
where exp.fexp_cancel_flag = 'N' and
exp.fexp_re is not null and
rowid in
(select rowid from(select rowid from fexpense t group by t.fexp_bill_no order by t.fexp_charge_id,t.fexp_currency_code ) where rownum<=1)