查看: 3281|回复: 12

这个SQL还有优化的余地吗?

[复制链接]
论坛徽章:
44
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-01-04 11:53:29ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562012新春纪念徽章
日期:2012-02-13 15:10:58
发表于 2011-3-4 14:45 | 显示全部楼层 |阅读模式
select b.jjh,
       b.hth,
       b.xmh,
       b.jg,
       b.bm,
       b.cpbm,
       b.cpmc,
       b.khmc,
       b.khjl,
       b.bz,
       (case
         when sum(a.bt + b.bt) < 0 then
          0
         else
          sum(a.bt + b.bt)
       end) bt
  from (select t.acct_num jjh,
               t.contract_num hth,
               t.project_name xmh,
               t.branch_name jg,
               t.party_name khmc,
               t.currency_name bz,
               (sum(nvl(tt.btmny_d, 0) + nvl(tt.badloanmny_d, 0) +
                    nvl(tt.ywmny_d, 0))) bt
          from eib_loanaccount t, eib_ratecomputeforloan tt
         where tt.rundate(+) = t.accountdate
           and tt.acct_no(+) = t.acct_num
           and (acct_num in (select acct_num
                               from eib_btratetable
                              where period >= '2010-01-01'
                                and period <= '2010-12-30') and
                (t.accountdate >= '2010-01-01' AND
                t.accountdate <= '2010-12-30' AND
                t.business_cd in
                (select trim(pk_selecteddata)
                    from bd_refdatatemp
                   where pk_corp = '1001'
                     and cuserid = '00015A1000000072X92V'
                     and refnodename = '产品范围'
                     and pk_ts = '10015A100000003HSHLW')) and
                t.pk_corp = '0001' and tt.pk_corp = '0001')
         group by t.acct_num,
                  t.contract_num,
                  t.project_name,
                  t.branch_name,
                  t.party_name,
                  t.currency_name) a,
       (select t.acct_num jjh,
               t.contract_num hth,
               t.project_name xmh,
               t.branch_name jg,
               t.dept_name bm,
               t.productcode cpbm,
               t.productname cpmc,
               t.party_name khmc,
               t.party_manager_name khjl,
               t.currency_name bz,
               (sum(nvl(tt.btmny_d, 0) + nvl(tt.badloanmny_d, 0) +
                    nvl(tt.ywmny_d, 0))) bt
          from eib_loanaccount t, eib_ratecomputeforloan tt
         where tt.rundate(+) = t.accountdate
           and tt.acct_no(+) = t.acct_num
           and (acct_num in (select acct_num
                               from eib_btratetable
                              where period = '2010-12-31') and
                (t.accountdate = '2010-12-31' AND
                t.business_cd in
                (select trim(pk_selecteddata)
                    from bd_refdatatemp
                   where pk_corp = '1001'
                     and cuserid = '00015A1000000072X92V'
                     and refnodename = '产品范围'
                     and pk_ts = '10015A100000003HSHLW')) and
                t.pk_corp = '0001' and tt.pk_corp = '0001')
         group by t.acct_num,
                  t.contract_num,
                  t.project_name,
                  t.branch_name,
                  t.dept_name,
                  t.productcode,
                  t.productname,
                  t.party_name,
                  t.party_manager_name,
                  t.currency_name) b
where b.jjh(+) = a.jjh
   and b.hth(+) = a.hth
group by b.jjh,
          b.hth,
          b.xmh,
          b.jg,
          b.bm,
          b.cpbm,
          b.cpmc,
          b.khmc,
          b.khjl,
          b.bz

执行计划如下:
SELECT STATEMENT, GOAL = ALL_ROWS                        Cost=3497        Cardinality=1        Bytes=226
HASH GROUP BY                        Cost=3497        Cardinality=1        Bytes=226
  HASH JOIN OUTER                        Cost=3496        Cardinality=1        Bytes=226
   VIEW        Object owner=NC502                Cost=2656        Cardinality=1        Bytes=47
    HASH GROUP BY                        Cost=2656        Cardinality=1        Bytes=340
     TABLE ACCESS BY INDEX ROWID        Object owner=NC502        Object name=EIB_RATECOMPUTEFORLOAN        Cost=51        Cardinality=1        Bytes=65
      NESTED LOOPS                        Cost=2655        Cardinality=1        Bytes=340
       NESTED LOOPS                        Cost=2604        Cardinality=1        Bytes=275
        MERGE JOIN CARTESIAN                        Cost=2370        Cardinality=1        Bytes=110
         SORT UNIQUE                        Cost=588        Cardinality=160040        Bytes=5281320
          TABLE ACCESS FULL        Object owner=NC502        Object name=EIB_BTRATETABLE        Cost=588        Cardinality=160040        Bytes=5281320
         BUFFER SORT                        Cost=0        Cardinality=1        Bytes=77
          SORT UNIQUE                        Cost=1        Cardinality=1        Bytes=77
           TABLE ACCESS BY INDEX ROWID        Object owner=NC502        Object name=BD_REFDATATEMP        Cost=1        Cardinality=1        Bytes=77
            INDEX RANGE SCAN        Object owner=NC502        Object name=I_REFDATATEMP1        Cost=1        Cardinality=1       
        TABLE ACCESS BY INDEX ROWID        Object owner=NC502        Object name=EIB_LOANACCOUNT        Cost=234        Cardinality=4        Bytes=660
         INDEX RANGE SCAN        Object owner=NC502        Object name=IDX_EIB_LOANACCOUNT        Cost=1        Cardinality=580       
       INDEX RANGE SCAN        Object owner=NC502        Object name=IDX_DATE        Cost=15        Cardinality=5731       
   VIEW        Object owner=NC502                Cost=840        Cardinality=1        Bytes=179
    HASH GROUP BY                        Cost=840        Cardinality=1        Bytes=343
     TABLE ACCESS BY INDEX ROWID        Object owner=NC502        Object name=EIB_LOANACCOUNT        Cost=2        Cardinality=1        Bytes=190
      NESTED LOOPS                        Cost=839        Cardinality=1        Bytes=343
       MERGE JOIN CARTESIAN                        Cost=668        Cardinality=106        Bytes=16218
        MERGE JOIN CARTESIAN                        Cost=82        Cardinality=1        Bytes=120
         SORT UNIQUE                        Cost=1        Cardinality=1        Bytes=77
          TABLE ACCESS BY INDEX ROWID        Object owner=NC502        Object name=BD_REFDATATEMP        Cost=1        Cardinality=1        Bytes=77
           INDEX RANGE SCAN        Object owner=NC502        Object name=I_REFDATATEMP1        Cost=1        Cardinality=1       
         BUFFER SORT                        Cost=81        Cardinality=865        Bytes=37195
          TABLE ACCESS BY INDEX ROWID        Object owner=NC502        Object name=EIB_RATECOMPUTEFORLOAN        Cost=80        Cardinality=865        Bytes=37195
           INDEX RANGE SCAN        Object owner=NC502        Object name=IDX_DATE        Cost=20        Cardinality=9660       
        BUFFER SORT                        Cost=588        Cardinality=517        Bytes=17061
         SORT UNIQUE                        Cost=586        Cardinality=517        Bytes=17061
          TABLE ACCESS FULL        Object owner=NC502        Object name=EIB_BTRATETABLE        Cost=586        Cardinality=517        Bytes=17061
       INDEX RANGE SCAN        Object owner=NC502        Object name=IDX_EIB_LOANACCOUNT        Cost=1        Cardinality=1       

对此SQL进行了优化:
select b.jjh,
       b.hth,
       b.xmh,
       b.jg,
       b.bm,
       b.cpbm,
       b.cpmc,
       b.khmc,
       b.khjl,
       b.bz,
       (case
         when sum(a.bt + b.bt) < 0 then
          0
         else
          sum(a.bt + b.bt)
       end) bt
  from (select t.acct_num jjh,
               t.contract_num hth,
               t.project_name xmh,
               t.branch_name jg,
               t.party_name khmc,
               t.currency_name bz,
               ((nvl(tt.btmny_d, 0) + nvl(tt.badloanmny_d, 0) +
                    nvl(tt.ywmny_d, 0))) bt
          from eib_loanaccount t, eib_ratecomputeforloan tt
         where tt.rundate(+) = t.accountdate
           and tt.acct_no(+) = t.acct_num
           and (acct_num in (select acct_num
                               from eib_btratetable
                              where period >= '2010-01-01'
                                and period <= '2010-12-30') and
                (t.accountdate >= '2010-01-01' AND
                t.accountdate <= '2010-12-30' AND
                t.business_cd in
                (select trim(pk_selecteddata)
                    from bd_refdatatemp
                   where pk_corp = '1001'
                     and cuserid = '00015A1000000072X92V'
                     and refnodename = '产品范围'
                     and pk_ts = '10015A100000003HSHLW')) and
                t.pk_corp = '0001' and tt.pk_corp = '0001')) a,
       (select t.acct_num jjh,
               t.contract_num hth,
               t.project_name xmh,
               t.branch_name jg,
               t.dept_name bm,
               t.productcode cpbm,
               t.productname cpmc,
               t.party_name khmc,
               t.party_manager_name khjl,
               t.currency_name bz,
               ((nvl(tt.btmny_d, 0) + nvl(tt.badloanmny_d, 0) +
                    nvl(tt.ywmny_d, 0))) bt
          from eib_loanaccount t, eib_ratecomputeforloan tt
         where tt.rundate(+) = t.accountdate
           and tt.acct_no(+) = t.acct_num
           and (acct_num in (select acct_num
                               from eib_btratetable
                              where period = '2010-12-31') and
                (t.accountdate = '2010-12-31' AND
                t.business_cd in
                (select trim(pk_selecteddata)
                    from bd_refdatatemp
                   where pk_corp = '1001'
                     and cuserid = '00015A1000000072X92V'
                     and refnodename = '产品范围'
                     and pk_ts = '10015A100000003HSHLW')) and
                t.pk_corp = '0001' and tt.pk_corp = '0001')
) b
where b.jjh(+) = a.jjh
   and b.hth(+) = a.hth
group by b.jjh,
          b.hth,
          b.xmh,
          b.jg,
          b.bm,
          b.cpbm,
          b.cpmc,
          b.khmc,
          b.khjl,
          b.bz

执行计划如下:
SELECT STATEMENT, GOAL = ALL_ROWS                        Cost=3326        Cardinality=1        Bytes=636
HASH GROUP BY                        Cost=3326        Cardinality=1        Bytes=636
  NESTED LOOPS OUTER                        Cost=3325        Cardinality=1        Bytes=636
   NESTED LOOPS                        Cost=2655        Cardinality=1        Bytes=276
    NESTED LOOPS                        Cost=2604        Cardinality=1        Bytes=211
     MERGE JOIN CARTESIAN                        Cost=2370        Cardinality=1        Bytes=110
      SORT UNIQUE                        Cost=588        Cardinality=160040        Bytes=5281320
       TABLE ACCESS FULL        Object owner=NC502        Object name=EIB_BTRATETABLE        Cost=588        Cardinality=160040        Bytes=5281320
      BUFFER SORT                        Cost=0        Cardinality=1        Bytes=77
       SORT UNIQUE                        Cost=1        Cardinality=1        Bytes=77
        TABLE ACCESS BY INDEX ROWID        Object owner=NC502        Object name=BD_REFDATATEMP        Cost=1        Cardinality=1        Bytes=77
         INDEX RANGE SCAN        Object owner=NC502        Object name=I_REFDATATEMP1        Cost=1        Cardinality=1       
     TABLE ACCESS BY INDEX ROWID        Object owner=NC502        Object name=EIB_LOANACCOUNT        Cost=234        Cardinality=4        Bytes=404
      INDEX RANGE SCAN        Object owner=NC502        Object name=IDX_EIB_LOANACCOUNT        Cost=1        Cardinality=580       
    TABLE ACCESS BY INDEX ROWID        Object owner=NC502        Object name=EIB_RATECOMPUTEFORLOAN        Cost=51        Cardinality=1        Bytes=65
     INDEX RANGE SCAN        Object owner=NC502        Object name=IDX_DATE        Cost=15        Cardinality=5731       
   VIEW PUSHED PREDICATE        Object owner=NC502                Cost=670        Cardinality=1        Bytes=360
    TABLE ACCESS BY INDEX ROWID        Object owner=NC502        Object name=EIB_LOANACCOUNT        Cost=2        Cardinality=1        Bytes=211
     NESTED LOOPS                        Cost=670        Cardinality=1        Bytes=364
      MERGE JOIN CARTESIAN                        Cost=668        Cardinality=1        Bytes=153
       MERGE JOIN CARTESIAN                        Cost=81        Cardinality=1        Bytes=120
        TABLE ACCESS BY INDEX ROWID        Object owner=NC502        Object name=EIB_RATECOMPUTEFORLOAN        Cost=80        Cardinality=1        Bytes=43
         INDEX RANGE SCAN        Object owner=NC502        Object name=IDX_DATE        Cost=20        Cardinality=9660       
        BUFFER SORT                        Cost=0        Cardinality=1        Bytes=77
         SORT UNIQUE                        Cost=1        Cardinality=1        Bytes=77
          TABLE ACCESS BY INDEX ROWID        Object owner=NC502        Object name=BD_REFDATATEMP        Cost=1        Cardinality=1        Bytes=77
           INDEX RANGE SCAN        Object owner=NC502        Object name=I_REFDATATEMP1        Cost=1        Cardinality=1       
       BUFFER SORT                        Cost=667        Cardinality=1        Bytes=33
        SORT UNIQUE                        Cost=587        Cardinality=1        Bytes=33
         TABLE ACCESS FULL        Object owner=NC502        Object name=EIB_BTRATETABLE        Cost=587        Cardinality=1        Bytes=33
      INDEX RANGE SCAN        Object owner=NC502        Object name=IDX_EIB_LOANACCOUNT        Cost=1        Cardinality=1
论坛徽章:
44
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-01-04 11:53:29ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562012新春纪念徽章
日期:2012-02-13 15:10:58
 楼主| 发表于 2011-3-4 14:49 | 显示全部楼层
优化思想是把里面的两个GROUP BY去掉,因为最外面会进行GROUP BY操作
另外,
create index IDX1_EIB_RATECOMPUTEFORLOAN on EIB_RATECOMPUTEFORLOAN (acct_no);
create index IDX2_EIB_RATECOMPUTEFORLOAN on EIB_RATECOMPUTEFORLOAN (rundate);

create index idx1_eib_loanaccount on eib_loanaccount(accountdate);
create index idx2_eib_loanaccount on eib_loanaccount(acct_num);
create index idx3_eib_loanaccount on eib_loanaccount(pk_corp);

优化后执行一次时间还很长,大家还有其它建议吗?

使用道具 举报

回复
论坛徽章:
14
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:52沸羊羊
日期:2015-03-04 14:43:43马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11福特
日期:2013-10-14 21:18:25凯迪拉克
日期:2013-09-23 23:01:572013年新春福章
日期:2013-02-25 14:51:242012新春纪念徽章
日期:2012-01-04 11:49:54ITPUB十周年纪念徽章
日期:2011-11-01 16:19:412011新春纪念徽章
日期:2011-02-18 11:43:33
发表于 2011-3-4 15:17 | 显示全部楼层
EIB_BTRATETABLE 這張表的period 字段設計的相當不合理,如果用DATE類型,可以考慮把它改成分區表

使用道具 举报

回复
论坛徽章:
44
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-01-04 11:53:29ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562012新春纪念徽章
日期:2012-02-13 15:10:58
 楼主| 发表于 2011-3-4 15:21 | 显示全部楼层
这个表的数据量倒不是很大,一二十万
EIB_RATECOMPUTEFORLOAN和eib_loanaccount都300多万

使用道具 举报

回复
论坛徽章:
4
2012新春纪念徽章
日期:2012-01-04 11:56:01
发表于 2011-3-4 15:29 | 显示全部楼层
學習了!

使用道具 举报

回复
论坛徽章:
14
ITPUB9周年纪念徽章
日期:2010-10-08 09:28:52沸羊羊
日期:2015-03-04 14:43:43马上有车
日期:2014-02-18 16:41:112014年新春福章
日期:2014-02-18 16:41:11福特
日期:2013-10-14 21:18:25凯迪拉克
日期:2013-09-23 23:01:572013年新春福章
日期:2013-02-25 14:51:242012新春纪念徽章
日期:2012-01-04 11:49:54ITPUB十周年纪念徽章
日期:2011-11-01 16:19:412011新春纪念徽章
日期:2011-02-18 11:43:33
发表于 2011-3-4 15:40 | 显示全部楼层

回复 #4 jlliuyi 的帖子

EIB_RATECOMPUTEFORLOAN表上建一個acct_no和rundate組合索引看看如何

EIB_BTRATETABLE的period字段可能要收集一下histogram的信息

使用道具 举报

回复
论坛徽章:
44
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-01-04 11:53:29ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562012新春纪念徽章
日期:2012-02-13 15:10:58
 楼主| 发表于 2011-3-4 15:51 | 显示全部楼层
没错,合理的建立复合索引是有可能提高效率
:)

使用道具 举报

回复
论坛徽章:
44
2015年新春福章
日期:2015-03-06 11:57:312012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-02-13 15:10:582012新春纪念徽章
日期:2012-01-04 11:53:29ITPUB十周年纪念徽章
日期:2011-11-01 16:23:26管理团队成员
日期:2011-05-07 01:45:082011新春纪念徽章
日期:2011-01-25 15:42:562012新春纪念徽章
日期:2012-02-13 15:10:58
 楼主| 发表于 2011-3-4 16:11 | 显示全部楼层
还有没有其它好的建议!

使用道具 举报

回复
论坛徽章:
0
发表于 2011-3-4 17:47 | 显示全部楼层
我记得exists是不是效率比in要好一点。

使用道具 举报

回复
论坛徽章:
4
迷宫蛋
日期:2012-11-23 14:50:28马上有钱
日期:2014-08-22 16:29:48双鱼座
日期:2016-01-26 21:22:03ITPUB15周年纪念
日期:2017-11-28 15:31:33
发表于 2011-3-4 18:35 | 显示全部楼层
路过学习学习

使用道具 举报

回复

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

本版积分规则 发表回复

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号
  
快速回复 返回顶部 返回列表