DBLINK数据计算优化

今天在调用线上数据库统计数据时，运行了好久都计算不出结果。

原语句：

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那端执行，结果迅速的计算出来了。