现在用这个查询要将近20分钟，希望能在5分钟之内查出来，数据量很大

Question

select gg.mer_id brhId, bb.brh_nm brhIdDesc, sum(case
when gg.txn_flag in ('00') then
gg.tran_amt
else
0
end) zhiFuTotalAmt, sum(case
when gg.txn_flag in ('00') then
1
else
0
end) zhiFuTotalCount, sum(case
when gg.txn_flag in ('02') then
gg.tran_amt
else
0
end) tuiHuoTotalAmt, sum(case
when gg.txn_flag in ('02') then
1
else
0
end) tuiHuoTotalCount from ((select a.pay_brh_id,
b.mer_id,  

a.txn_amt as tran_amt,
'02' as txn_flag
from tbl_pgw_refund_dtl a,
tbl_pgw_txn_dtl b,
tbl_pgw_refund_info t
where a.order_id = b.order_id
and a.refund_req = t.refund_req
and t.result in ('4', '7')
and t.refund_type != '1'
and t.refund_dt >= 20130401
and t.refund_dt <= 20130423
and b.mer_id !='888888888888888'
and a.pay_brh_id = 888880188102900) union all
(select d.bank_id as pay_brh_id, c.mer_id, d.tran_amt, '00' as txn_flag
from TBL_PGW_TXN_DTL c, TBL_PGW_PAY_DTL d
where c.order_id = d.order_id
and c.rsp_code = '0000'
and c.int_txn_dt >= 20130401
and c.int_txn_dt <= 20130423
and c.mer_id !='888888888888888'
and d.bank_id = 888880188102900
and d.trans_code in ('A0160', 'A0400')) union all
(select a.pay_brh_id, f.send_brh_id as mer_id, a.txn_amt as tran_amt, '02' as txn_flag from tbl_pgw_refund_dtl a, tbl_pgw_txn_dtl b, tbl_pgw_refund_info t, tbl_csys_txn_log f where a.order_id = b.order_id
and b.order_id = f.order_id
and a.refund_req = t.refund_req
and t.result in ('4', '7')
and t.refund_type != '1'
and t.refund_dt >= 20130401
and t.refund_dt <= 20130423
and a.pay_brh_id = 888880188102900) union all
(select d.bank_id as pay_brh_id,
t.send_brh_id as mer_id,
d.tran_amt,
'00' as txn_flag
from TBL_PGW_TXN_DTL c, TBL_PGW_PAY_DTL d,
tbl_csys_txn_log t
where c.order_id = d.order_id
and c.order_id = t.order_id
and c.rsp_code = '0000'
and c.int_txn_dt >= 20130401
and c.int_txn_dt <= 20130423
and d.bank_id = 888880188102900
and d.trans_code in ('A0160', 'A0400'))
) gg, tbl_saf_brh_info bb where gg.mer_id = bb.brh_id(+)
group by gg.mer_id, bb.brh_nm

Answer 1

tbl_pgw_refund_info:refund_req,resulttbl_pgw_refund_dtl :pay_brh_idtbl_pgw_txn_dtl :order_idTBL_PGW_TXN_DTL :int_txn_dt,rsp_codeTBL_PGW_PAY_DTL :order_id在上述表的指定字段上建索引

Answer 2

把语句中用到in的都改成exists比如&nbsp;select&nbsp;a,b&nbsp;from&nbsp;T&nbsp;where&nbsp;id&nbsp;in&nbsp;(1)&nbsp;&nbsp;改成 select&nbsp;a,b&nbsp;from&nbsp;T&nbsp;t&nbsp;where&nbsp;exsits(&nbsp;select&nbsp;1&nbsp;from&nbsp;&nbsp;t.id&nbsp;=&nbsp;1) 你也可以查一下&nbsp;SQL优化中有一点&nbsp;就是把int&nbsp;转化成&nbsp;exsits