联系:手机/微信(+86 17813235971) QQ(107644445)
标题:诡异dblink问题解决
作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
一、诡异dblink起源
今天开发拿过来一条sql,说有诡异现象,sql如下
INSERT INTO TAB_CS_CALLLOG select c.user_logon,/*工号*/c.user_name,/*姓名*/a.call_id,/*通话id*/ a.caller_nbr,/*主叫号码*/a.called_nbr,/*被叫号码*/d.start_time,/*呼入时间*/ b.call_time,/*接听时间*/b.end_time,/*结束时间*/b.call_dura,/*时长*/ to_number(b.call_time-d.start_time)*24*3600,/*等待时长*/ decode(c.user_logon,null,0,1),/*类型*/ case when substr (a.CALLED_NBR,7, 2) = '00' then '广东移动' when substr (a.CALLED_NBR,7, 2) = '01' then '浙江移动' when substr (a.CALLED_NBR,7, 2) = '02' then '福建' when substr (a.CALLED_NBR,7, 2) = '03' then '四川' when substr (a.CALLED_NBR,7, 2) = '04' then '河南' when substr (a.CALLED_NBR,7, 2) = '05' then '湖北' when substr (a.CALLED_NBR,7, 2) = '06' then '北京CSIP' when substr (a.CALLED_NBR,7, 2) = '07' then '陕西' when substr (a.CALLED_NBR,7, 2) = '08' then '吉林' when substr (a.CALLED_NBR,7, 2) = '09' then '江西' when substr (a.CALLED_NBR,7, 2) = '10' then '宁夏' when substr (a.CALLED_NBR,7, 2) = '11' then '太原' when substr (a.CALLED_NBR,7, 2) = '12' then '江苏移动' when substr (a.CALLED_NBR,7, 2) = '13' then 'e掌管' when substr (a.CALLED_NBR,7, 2) = '15' then 'e多商' when substr (a.CALLED_NBR,7, 2) = '18' then '江苏联通' end 区域 from cscnew.a@cs a,cscnew.b@cs b,cscnew.c@cs c,cscnew.d@cs d where a.call_serial=b.call_serial(+) and b.call_serial=d.call_serial(+) and b.user_id=c.user_id(+) and substr(a.CALLED_NBR,1, 6) = '951654' and length (a.CALLED_NBR) = 15 and b.fail_reason is null and a.end_time>=to_date('20110822000000','yyyymmddhh24miss') and a.end_time<to_date('20110823000000','yyyymmddhh24miss');
然后我进行了测试,证实了她所说的诡异:
1、直接执行select语句需要1.7S左右,但是加上insert inot后,执行时间需要6分钟
2、直接select结果集为602条,加上insert into后,结果集为598条(少4条),如果直接执行select,除掉and b.fail_reason is null限制条件也刚好602条
二、查询相关资料,得到dblink的一些解释
1、dblink执行有两种方式,一种是在远处数据库执行完,然后结果返回,另一种是把远程的表下载到本来,然后执行
2、如果把远程的表下载到本地,空值或者null可能会发生变化(怀疑是空值转化为null,未证实)
三、根据这些解释,进行猜想
1、只执行select的时候,应该是在远程执行完,传输结果回来;而执行insert into的时候,是把远程的表全部下载到本地,然后执行出结果,而数据量本身比较大,所以比较慢
2、在把表从远程下载到本地的过程中,fail_reason 列的null值可以发生了变化,或者空值变为了null,所以数据多了4条
四、事实证明猜想
1、查询远程表大小,发现a表50m,b表400m,c表10m,d表100m左右,传输过来需要一定的时间
2、既然猜测是由于要把表传输到本地而导致这样的结果产生,那么处理方法就是让程序在远程计算出结果,然后传输到本地,查询了一些资料,上面说insert into会导致driving_site提示无效,那么我想到一个用视图的办法解决这个问题:在目标端建立一个关于本查询中无参数的视图,然后在本地通过dblink调用视图,这样总该先在远程执行出结果传输到本地了吧。
2.1)建立目标端视图
create or replace view v_tab select c.user_logon,/*工号*/c.user_name,/*姓名*/a.call_id,/*通话id*/ a.caller_nbr,/*主叫号码*/a.called_nbr,/*被叫号码*/d.start_time,/*呼入时间*/ b.call_time,/*接听时间*/b.end_time,/*结束时间*/b.call_dura,/*时长*/ to_number(b.call_time-d.start_time)*24*3600,/*等待时长*/ decode(c.user_logon,null,0,1),/*类型*/ case when substr (a.CALLED_NBR,7, 2) = '00' then '广东移动' when substr (a.CALLED_NBR,7, 2) = '01' then '浙江移动' when substr (a.CALLED_NBR,7, 2) = '02' then '福建' when substr (a.CALLED_NBR,7, 2) = '03' then '四川' when substr (a.CALLED_NBR,7, 2) = '04' then '河南' when substr (a.CALLED_NBR,7, 2) = '05' then '湖北' when substr (a.CALLED_NBR,7, 2) = '06' then '北京CSIP' when substr (a.CALLED_NBR,7, 2) = '07' then '陕西' when substr (a.CALLED_NBR,7, 2) = '08' then '吉林' when substr (a.CALLED_NBR,7, 2) = '09' then '江西' when substr (a.CALLED_NBR,7, 2) = '10' then '宁夏' when substr (a.CALLED_NBR,7, 2) = '11' then '太原' when substr (a.CALLED_NBR,7, 2) = '12' then '江苏移动' when substr (a.CALLED_NBR,7, 2) = '13' then 'e掌管' when substr (a.CALLED_NBR,7, 2) = '15' then 'e多商' when substr (a.CALLED_NBR,7, 2) = '18' then '江苏联通' end 区域 from cscnew.a a,cscnew.b b,cscnew.c c,cscnew.d d where a.call_serial=b.call_serial(+) and b.call_serial=d.call_serial(+) and b.user_id=c.user_id(+) and substr(a.CALLED_NBR,1, 6) = '951654' and length (a.CALLED_NBR) = 15 and b.fail_reason is null
2.2)本地调用远程视图
INSERT INTO TAB_CS_CALLLOG select * from v_tab@cs a where a.end_time>=to_date('20110822000000','yyyymmddhh24miss') and a.end_time<to_date('20110823000000','yyyymmddhh24miss');
2.3)执行结果2.3S完成数据插入,而且条数也是598条,证明我的猜想是正确的,更重要的是解决了今天这个让人疑惑的问题
细节方面的问题确实值得深入思考,确实思维方法值得学习。