查看: 5338|回复: 5

[性能调整] left join 的优化问题

[复制链接]
认证徽章
论坛徽章:
5
2010新春纪念徽章
日期:2010-03-01 11:20:51ITPUB学员
日期:2010-04-14 14:24:31ITPUB学员
日期:2010-08-21 18:20:28ITPUB9周年纪念徽章
日期:2010-10-08 09:31:212011新春纪念徽章
日期:2011-02-18 11:42:49
发表于 2010-1-7 17:32 | 显示全部楼层 |阅读模式
现有一条sql:
select * from (select x.int_id
  from table1 x, table2 y
where x.cabinet_id = y.sc_id
   and y.cabinet_id = 109) x
left join(
select a.int_id, a.NETWORK_STATUS
  from table3 a,
       (select int_id, max(org_time) AS org_time
          from table3
         where org_time > sysdate - 1
           and int_id in (select x.int_id
                            from table1  x, table2 y
                           where x.cabinet_id = y.sc_id
                             and y.cabinet_id = 109)
         group by int_id) b
where a.org_time = b.org_time
   and a.int_id = b.int_id) y on x.int_id=y.int_id;

执行计划如下:
SELECT STATEMENT, GOAL = CHOOSE                          Cost=421        Cardinality=337        Bytes=17861
HASH JOIN                                                          Cost=421        Cardinality=337        Bytes=17861
  NESTED LOOPS OUTER                                          Cost=416        Cardinality=206        Bytes=5562
   TABLE ACCESS FULL     Object name=table1                          Cost=4                   Cardinality=206        Bytes=2266
   VIEW PUSHED PREDICATE                                               Cost=2                        Cardinality=1        Bytes=16
    TABLE ACCESS BY LOCAL INDEX ROWID   Object name=table3        Cost=3        Cardinality=1        Bytes=18
     NESTED LOOPS                                                                        Cost=959        Cardinality=1        Bytes=34
      VIEW                                                                                         Cost=546        Cardinality=206        Bytes=3296
       SORT GROUP BY                                          Cost=546        Cardinality=206        Bytes=10712
        HASH JOIN                                                          Cost=280        Cardinality=56648        Bytes=2945696
         TABLE ACCESS FULL                     Object name=table2        Cost=4        Cardinality=21        Bytes=546
         HASH JOIN                                                          Cost=275        Cardinality=34672        Bytes=901472
          TABLE ACCESS FULL                         Object name=table2        Cost=4        Cardinality=206        Bytes=2266
          PARTITION RANGE ITERATOR                                       
           INDEX RANGE SCAN                     Object name=table3_ind        Cost=270        Cardinality=260710        Bytes=3910650
      PARTITION RANGE ITERATOR                                       
       INDEX RANGE SCAN                Object name=table3_ind         Cost=2        Cardinality=1       
  TABLE ACCESS FULL                Object name=table2        Cost=4        Cardinality=21        Bytes=546

x结果集是87条,y结果集是84条,table1,table2数据量都比较少(1000条左右),但是table3的数据量比较大,有5000W条左右,table3的索引table3_ind(org_time,int_id),现在情况是x left join y执行时间超长(具体时间不知,能不能有结果也不知道。。。),而y left join x的时间就比较短。
请问各位大虾:
1)为什么会出现上述情况?
2)如何优化?
谢谢

[ 本帖最后由 linyisen1985 于 2010-1-7 17:36 编辑 ]
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2010-1-7 18:27 | 显示全部楼层
select *
  from (select x.int_id
          from table1 x, table2 y
         where x.cabinet_id = y.sc_id
           and y.cabinet_id = 109) x
  left join (select *
               from (select a.int_id,
                            a.NETWORK_STATUS,
                            row_number() over(partition by inst_id order by org_time desc) as rn,
                            org_time
                       from table3
                      where org_time > sysdate - 1
                        and int_id in (select x.int_id
                                         from table1 x, table2 y
                                        where x.cabinet_id = y.sc_id
                                          and y.cabinet_id = 109))
              where rn = 1) y
    on x.int_id = y.int_id;

使用道具 举报

回复
论坛徽章:
400
紫蛋头
日期:2012-05-21 10:19:41迷宫蛋
日期:2012-06-06 16:02:49奥运会纪念徽章:足球
日期:2012-06-29 15:30:06奥运会纪念徽章:排球
日期:2012-07-10 21:24:24鲜花蛋
日期:2012-07-16 15:24:59奥运会纪念徽章:拳击
日期:2012-08-07 10:54:50奥运会纪念徽章:羽毛球
日期:2012-08-21 15:55:33奥运会纪念徽章:蹦床
日期:2012-08-21 21:09:51奥运会纪念徽章:篮球
日期:2012-08-24 10:29:11奥运会纪念徽章:体操
日期:2012-09-07 16:40:00
发表于 2010-1-7 21:31 | 显示全部楼层
分析函数好

使用道具 举报

回复
认证徽章
论坛徽章:
5
2010新春纪念徽章
日期:2010-03-01 11:20:51ITPUB学员
日期:2010-04-14 14:24:31ITPUB学员
日期:2010-08-21 18:20:28ITPUB9周年纪念徽章
日期:2010-10-08 09:31:212011新春纪念徽章
日期:2011-02-18 11:42:49
发表于 2010-1-8 10:03 | 显示全部楼层
不好意思,昨天下班了没看到,今天试了一下,棉花糖的SQL确是能执行出来,不过要2s多的时间,我把left join后面的SQL我改成
select a.int_id, a.NETWORK_STATUS
  from table3 a,
       (select q.int_id, max(q.org_time) AS org_time
          from table3 q, table1 w, table2 e
         where q.org_time > sysdate - 1
           And q.int_id = w.int_id
           and w.cabinet_id = e.sc_id
           and e.cabinet_id = 109
         group by q.int_id) b
where a.org_time = b.org_time
   and a.int_id = b.int_id;
执行时间只有1s,我想是in的问题,不知道有谁能解答一下,谢谢

使用道具 举报

回复
论坛徽章:
8
2010新春纪念徽章
日期:2010-03-01 11:08:26ITPUB9周年纪念徽章
日期:2010-10-08 09:32:272011新春纪念徽章
日期:2011-02-18 11:43:33奥运会纪念徽章:棒球
日期:2012-07-03 15:20:13奥运会纪念徽章:摔跤
日期:2012-07-22 21:22:28ITPUB 11周年纪念徽章
日期:2012-10-09 18:14:48紫蛋头
日期:2013-02-20 09:40:232013年新春福章
日期:2013-02-25 14:51:24
发表于 2010-1-8 10:13 | 显示全部楼层
from performance turning guide
In a regular outer join, the optimizer chooses the order of tables (driving and driven)
based on the cost. However, in a nested loop outer join, the order of tables is
determined by the join condition. The outer table, with rows that are being preserved,
is used to drive to the inner table.

使用道具 举报

回复
论坛徽章:
136
ITPUB年度最佳技术回答奖
日期:2010-06-12 13:17:14现代
日期:2013-10-02 14:53:59路虎
日期:2013-11-22 12:26:182014年新春福章
日期:2014-02-18 16:42:02马上有房
日期:2014-02-18 16:42:02马上有车
日期:2014-02-19 11:55:14马上有房
日期:2014-02-19 11:55:14马上有钱
日期:2014-02-19 11:55:14马上有对象
日期:2014-02-19 11:55:14马上加薪
日期:2014-02-19 11:55:14
发表于 2010-1-8 10:18 | 显示全部楼层

回复 #4 linyisen1985 的帖子

关键是改了以后含义一样不一样,你这语法上讲和原来的差别比较大

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则 发表回复

SACC2019中国系统架构师大会

【数字转型 架构演进】SACC2019中国系统架构师大会,7折限时优惠重磅来袭!
2019年10月31日~11月2日第11届中国系统架构师大会(SACC2019)将在北京隆重召开。四大主线并行的演讲模式,1个主会场、20个技术专场、超千人参与的会议规模,100+来自互联网、金融、制造业、电商等领域的嘉宾阵容,将为广大参会者提供一场最具价值的技术交流盛会。

限时七折期:2019年8月31日前


----------------------------------------

大会官网>>
TOP技术积分榜 社区积分榜 徽章 团队 统计 知识索引树 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 
京ICP备09055130号-4  北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表