db2 sql语句优化

select a.entrustdate,a.busseqno,a.bustype,a.vouchno,a.sendbank,a.payeracc,a.payername,a.payeraddr,a.currency,a.amount,a.payeeacc,a.payeename,a.recvbank,a.payeeaddr,a.postscript,a.printcnt,a.agentserialno,a.tradebusistep||a.tradestatus,b.drbankno,b.crbankno,b.bdstmagno,b.bondco,b.npamt,b.bondint,b.adenamg,b.refrtset,b.rendat,b.reint,b.dcflag,b.txnbatchno,b.bussubtype,g.recvbank,g.payeeacc,g.payeename,h.banksysseq from afa_maintransdtl a left join ext_transbook b on a.workdate=b.workdate and a.agentserialno=b.agentserialno left join ext_insusbook as g on a.workdate=g.oworkdate and a.agentserialno=g.oagentserialno left join afa_mainaction as h on a.workdate=h.workdate and a.agentserialno=h.agentserialno and a.tradestep=h.tradestep where a.sysid='000001' and ( ( a.brno='320099900' and a.sysid='000001' and a.mbflag='1' and a.workdate between '20120325' and '20120331' and ( (a.tradebusistep='U' and a.tradestatus='1') or (a.tradebusistep='V' and a.tradestatus='1') ) ) or ( a.workdate<='20120331' and (a.workdate,a.agentserialno) in (select f.oworkdate,f.oagentserialno from ext_insusbook as f where f.sysid='000001' and f.recvbank='402301099998' and f.status='1' and f.workdate>='20120325' and f.workdate<='20120331' and f.mbflag='1') ) ) and a.agentserialno>'0' order by a.agentserialno asc fetch first 6 rows only 这条语句执行花了17秒，其中a表的brno是索引，a表的sysid是索引，a表的workdate是索引，求高手帮忙优化下

补充：a表的数据量大概在200万左右

追问：其他几个表都分别有索引也用到了,主要是后面那段workdate,agentserialno in f表的时候，效率太低了

答案：b/g/h三个表的workdate有索引么？另外，1.如果可能最好将所有联表用的字段都进行索引，2.（workdate，agentserialno）可以考虑二维索引，前提是空间足够的话，这样效率提高会非常明显。

其他：麻.烦`采纳.··.·`··^ select name from user. 给分吧 看能不能把这个语句的执行计划图给出来。看你的语句你试试能不能把left join取消。