今天在调用线上数据库统计数据时,运行了好久都计算不出结果。
原语句:
select *
from (select p.user_id,
u.user_acount,
p.points_balance,
p.points_deal,
p.points_all,
p.points_consume,
(select sum(expect_points)
from PC.t_OE@CB_readonly t
where t.order_state in (0,
4
--,1,2,3,5,6,7,8
)
and t.u_id = p.user_id
group by t.u_id) s_expect_points
from PC.t_OP@CB_readonly p
left join PC.t_OU@CB u
on u.id = p.user_id)
where s_expect_points <> points_deal
-------------------
仔细发现执行过程是先算出内部查询的结果,再外部计算,中间会经过网络运算,时间慢,后来改为以下语句
select *
from (select p.user_id,
u.user_acount,
p.points_balance,
p.points_deal,
p.points_all,
p.points_consume,
sum(case when (o.order_state=0 or o.order_state=4) then o.expect_points else 0 end ) s_expect_points
from PC.t_OP@CB_readonly p
left join PC.t_OU@CB_readonly u
on u.id = p.user_id
left join PC.t_OE@CB_readonly o
on o.u_id=p.user_id
group by p.user_id,
u.user_acount,
p.points_balance,
p.points_deal,
p.points_all,
p.points_consume
)
where s_expect_points <> points_deal;
把运算都放在DBLINK那端执行,结果迅速的计算出来了。