今天生产上遇到一个非常诡异的问题具体如下：
select * from(select ipagetable.*,rownum as ipagerownum from (Select a.cifno, a.cifname, a.appno, a.loantype, a.loantime, a.assuretype, a.loanamt, a.appstate, a.traceno,a.appstep,getappidea(a.appno,a.appstep,a.traceno,:L0 ), a.TermMonth,a.TermDay,a.adscbrno,a.apptype,a.popeflag,a.cifgrade,GETAppStepFlage(:L1 ,a.appstep), a.confsum, a.creditName,a.timelist,curtotalAmt ,a.creditNo from cr_loanapp a where a.appstate in (:L2 ,:L3 )
and getbatchflag(loantype)!=:L4 and a.apptype in( :L5 ,:L6 ) and a.loantype in (:L7 ,:L8 ) and a.adscbrno in (SELECT brno from bctl START WITH BRNO =:L9 CONNECT BY PRIOR BRNO = UPONEORG ) and ( a.Appstep = :L10 or a.Appstep=:L11 ) order by a.putdate, a.adscbrno)ipagetable) where ipagerownum>:L12 and ipagerownum <=:L13
这个sql 从监控来看执行非常的慢
select * from(select ipagetable.*,rownum as ipagerownum from (Select a.cifno, a.cifname, a.appno, a.loantype, a.loantime, a.assuretype, a.loanamt, a.appstate, a.traceno,a.appstep,getappidea(a.appno,a.appstep,a.traceno,:L0 ), a.TermMonth,a.TermDay,a.adscbrno,a.apptype,a.popeflag,a.cifgrade,GETAppStepFlage(:L1 ,a.appstep), a.confsum, a.creditName,a.timelist,curtotalAmt ,a.creditNo from cr_loanapp a where a.appstate in (:L2 ,:L3 )
and getbatchflag(loantype)!=:L4 and a.apptype in( :L5 ,:L6 ) and a.loantype in (:L7 ,:L8 ) and a.adscbrno in (SELECT brno from bctl START WITH BRNO =:L9 CONNECT BY PRIOR BRNO = UPONEORG ) and ( a.Appstep = :L10 or a.Appstep=:L11 ) order by a.putdate, a.adscbrno)ipagetable) where ipagerownum>:L12 and ipagerownum <=:L13
and getbatchflag(loantype)!=:L4 删除之后，执行的效率非常快[/i]
[i] 疑惑：db2的那个一个参数能影到function的执行效率？为什么在数据库重启之后，两个SQL的执行效率完全一样呢在线等待~~~~收起