下列oracle查询的效率不高，请高手帮忙。
with t1 as (
select Hl.lname, sum( to_number( Ht.org_txn_amt) - to_number( Ht.TXN_AMOUNT))/100 as HY ,count(1) as HC  
  from T_OfflineTradeDetails Ht  
  join t_card Hc on upper(Ht.drvcard_no)=hc.cw  
  join t_currentrelation Hcu on Hcu.cardno= Hc.cno  
  join t_line Hl on Hcu.lineid= Hl.lineid  
  where Ht.txn_flag in ('A4','87') and (Ht.card_type<>'06')  
  and ( Ht.TXN_DATE >='20100101' and Ht.TXN_DATE<='20130101') and ( Hl.lno in ('0988'))  
  group by Hl.lname
),
t2 as (
select Ll.lname, sum( to_number( Lt.org_txn_amt) - to_number( Lt.TXN_AMOUNT))/100 as LY ,count(1) as LC
  from T_OfflineTradeDetails Lt  
  join t_card Lc on upper(Lt.drvcard_no)=lc.cw  
  join t_currentrelation Lcu on Lcu.cardno= Lc.cno  
  join t_line Ll on Lcu.lineid= Ll.lineid where ( Lt.txn_flag in ('83','84') or ( Lt.txn_flag in ('A4','87') and (Lt.card_type='06') ))
  and ( Lt.TXN_DATE >='20100101' and Lt.TXN_DATE<='20130101') and ( Ll.lno in ('0988'))
  group by Ll.lname
)
select t1.lname, t1.hy, t1.hc, t2.ly, t2.lc from t1, t2 where t1.lname=t2.lname;

------解决方案--------------------
这样写的话，t1,t2表联合起来是没有索引走的，全部都是全表扫描

我看你的两个子查询都是相同的四个表，应该不用分开联合查询两次的吧，联合查询一次应可以的，因你第二个子查询和第一个条件只有一点不一样

试下把第一个表的条件用第二个表的 ( Lt.txn_flag in ('83','84') or ( Lt.txn_flag in ('A4','87') and (Lt.card_type='06') ))

然后计算sum和count时用case when来判断