下面这个语句我在plsql里执行用了28分钟,谁能帮我优化一下,感激不尽select to_char(tots.time,'yyyy-mm-dd'), count(1), sum(tots.fee)
from bkmsg_to_body_s tobs, bkmsg_to_transaction_s tots
where tots.body_id = tobs.body_id
and to_char(tots.time, 'yyyy-mm-dd hh24:mi:ss') between
'2012-06-01 00:00:00' and '2012-06-11 00:00:00'
and tobs.messageid || '_' || tobs.issuerid || '_' ||
tobs.serviceproviderid in
(select tcm.messageid || '_' || tccl.issuerid || '_' ||
tccl.serviceproviderid
from bkmsg_tc_messageid_r tcm, bkmsg_tc_clearitem_r tccl
where tcm.clearitem_id = tccl.clearitem_id)
group by to_char(tots.time, 'yyyy-mm-dd')
from bkmsg_to_body_s tobs, bkmsg_to_transaction_s tots
where tots.body_id = tobs.body_id
and to_char(tots.time, 'yyyy-mm-dd hh24:mi:ss') between
'2012-06-01 00:00:00' and '2012-06-11 00:00:00'
and tobs.messageid || '_' || tobs.issuerid || '_' ||
tobs.serviceproviderid in
(select tcm.messageid || '_' || tccl.issuerid || '_' ||
tccl.serviceproviderid
from bkmsg_tc_messageid_r tcm, bkmsg_tc_clearitem_r tccl
where tcm.clearitem_id = tccl.clearitem_id)
group by to_char(tots.time, 'yyyy-mm-dd')
tccl.serviceproviderid
分开按字段查询,不要拼结,拼结应不走索引的2.in用exists替换下3.where条件中的几个字段建下联合索引
1、四个表的数据量
2、表的主键,索引等信息
3、本sql的执行计划否则,任何优化都是瞎掰。
FROM Bkmsg_To_Body_s Tobs,
Bkmsg_To_Transaction_s Tots,
(SELECT Tcm.Messageid, Tccl.Issuerid, Tccl.Serviceproviderid
FROM Bkmsg_Tc_Messageid_r Tcm, Bkmsg_Tc_Clearitem_r Tccl
WHERE Tcm.Clearitem_Id = Tccl.Clearitem_Id) a
WHERE Tots.Body_Id = Tobs.Body_Id
AND Tobs.Messageid = a.Messageid
AND Tobs.Issuerid = a.Issuerid
AND Tobs.Serviceproviderid = a.Serviceproviderid
AND (Tots.TIME >= DATE '2012-06-01' AND Tots.TIME < DATE
'2012-06-11')
GROUP BY To_Char(Tots.TIME, 'yyyy-mm-dd')
from bkmsg_to_body_s tobs, bkmsg_to_transaction_s tots
where tots.body_id = tobs.body_id
and tots.time between
to_date('2012-06-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') and
to_date('2012-06-11 00:00:00', 'yyyy-mm-dd hh24:mi:ss')
and exists (
select 1
from bkmsg_tc_messageid_r tcm, bkmsg_tc_clearitem_r tccl
where tcm.clearitem_id = tccl.clearitem_id
and tcm.messageid = tobs.messageid
and tccl.issuerid = tobs.issuerid
and tccl.serviceproviderid = tobs.serviceproviderid)
group by to_char(tots.time, 'yyyy-mm-dd');