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

Question

select gg.mer_id brhId, bb.brh_nm brhIdDesc, sum(casewhen gg.txn_flag in ('00') thengg.tran_amtelse0end) zhiFuTotalAmt, sum(casewhen gg.txn_flag in ('00') then1else0end) zhiFuTotalCount, sum(casewhen gg.txn_flag in ('02') thengg.tran_amtelse0end) tuiHuoTotalAmt, sum(casewhen gg.txn_flag in ('02') then1else0end) tuiHuoTotalCount from ((select a.pay_brh_id,b.mer_id,  a.txn_amt as tran_amt,'02' as txn_flagfrom tbl_pgw_refund_dtl a,tbl_pgw_txn_dtl b,tbl_pgw_refund_info twhere a.order_id = b.order_idand a.refund_req = t.refund_reqand t.result in ('4', '7')and t.refund_type != '1'and t.refund_dt >= 20130401and t.refund_dt <= 20130423and 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_flagfrom TBL_PGW_TXN_DTL c, TBL_PGW_PAY_DTL dwhere c.order_id = d.order_idand c.rsp_code = '0000'and c.int_txn_dt >= 20130401and c.int_txn_dt <= 20130423and c.mer_id !='888888888888888'and d.bank_id = 888880188102900and 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_idand b.order_id = f.order_idand a.refund_req = t.refund_reqand t.result in ('4', '7')and t.refund_type != '1'and t.refund_dt >= 20130401and t.refund_dt <= 20130423and 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_flagfrom TBL_PGW_TXN_DTL c, TBL_PGW_PAY_DTL d,tbl_csys_txn_log twhere c.order_id = d.order_idand c.order_id = t.order_idand c.rsp_code = '0000'and c.int_txn_dt >= 20130401and c.int_txn_dt <= 20130423and d.bank_id = 888880188102900and 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,result

tbl_pgw_refund_dtl :pay_brh_id
tbl_pgw_txn_dtl :order_id

TBL_PGW_TXN_DTL :int_txn_dt,rsp_code

TBL_PGW_PAY_DTL :order_id
在上述表的指定字段上建索引

Answer 2

把语句中用到in的都改成exists

比如 select a,b from T where id in (1)  改成
select a,b from T t where exsits( select 1 from  t.id = 1)
你也可以查一下 SQL优化中有一点 就是把int 转化成 exsits