您好,登录后才能下订单哦!
密码登录
登录注册
点击 登录注册 即表示同意《亿速云用户服务条款》
分析:
由于优化器无法判断或获得远端表的统计信息,故原执行计划默认会采取把远程表(无论大小)拉到本地再连接的方式执行,
这样如果远程表较大的情况将会比较缓慢,像上述查询耗时在1分钟以上。
原SQL语句:
Select Wb.*, (Select Wi.Nextarrivedate From Mbs7_Oms.Xs_Warearriveinfo@Dc.Moonbasadb.Com Wi Where Wi.Warecode = Wb.Warecode) As Nextarrivedate From Mbs7_Crm.Wi_Warebase@Dc.Moonbasadb.Com Wb ---这里远端表较大 Inner Join (Select Wa.Stylecode, Max(Wa.Warecode) As Warecode From Mbs7_Crm.Wi_Warebase@Dc.Moonbasadb.Com Wa Inner Join (Select Stylecode From Dc_Support.Kh_Visitpage Vis Where Vis.Cuscode = :B1 And Vis.Addtime >= Trunc(Sysdate - 31) And Vis.Addtime < Trunc(Sysdate - 30) And Rownum <= 5 Order By Addtime Desc) Vis On Wa.Stylecode = Vis.Stylecode Group By Wa.Stylecode) Wc On Wb.Warecode = Wc.Warecode
解决方案:
用以下HINTS方式加上去后,优化器会调整执行计划,把运算端控制在WB,并且远端表MBS7_CRM.WI_WAREBASE表字段的索引(STYLECODE)缺少连接索引,于是在目标端创建以下索引进行优化,优化后COST从7百多下降到20,运行2秒内可返回结果,性能增加不少。
远端表创建索引:
create index mbs7_crm.ix_WI_WAREBASE_STYLECODE on mbs7_crm.WI_WAREBASE(STYLECODE)
调优后的SQL:
Select /*+DRIVING_SITE(WB)*/ Wb.*, (Select Wi.Nextarrivedate From Mbs7_Oms.Xs_Warearriveinfo@Dc.Moonbasadb.Com Wi Where Wi.Warecode = Wb.Warecode) As Nextarrivedate From Mbs7_Crm.Wi_Warebase@Dc.Moonbasadb.Com Wb ---远端表较大 Inner Join (Select Wa.Stylecode, Max(Wa.Warecode) As Warecode From Mbs7_Crm.Wi_Warebase@Dc.Moonbasadb.Com Wa Inner Join (Select Stylecode From Dc_Support.Kh_Visitpage Vis Where Vis.Cuscode = :B1 And Vis.Addtime >= Trunc(Sysdate - 31) And Vis.Addtime < Trunc(Sysdate - 30) And Rownum <= 5 Order By Addtime Desc) Vis On Wa.Stylecode = Vis.Stylecode Group By Wa.Stylecode) Wc On Wb.Warecode = Wc.Warecode
©版权声明:本文为 天凯DBS 的原创文章,转载请附上原文出处链接及本声明,否则将追究法律责任。
原文链接: https://dbs-service.cn/a/173.html
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。