圆月山庄资源网

oracle中使用group by优化distinct
数据库 2024/12/23 佚名
3 2
圆月山庄资源网 Design By www.vgjia.com

今天mentor给了一个sql语句优化的任务。（环境是sql developer）有一个语句执行很慢，查询出来的结果有17544条记录，但需970秒，速度很慢。语句是这样的：

SELECT DISTINCT  'AMEND_NEW', 
       reporttitle, 
       reportsubtitle, 
       cab_cab_transactions.branchcode, 
       cab_cab_transactions.prtfo_cd, 
       cab_cab_transactions.sstm_scrty_id, 
       cab_cab_transactions.sstm_trx_id, 
       cab_cab_transactions.trde_dttm, 
       cab_cab_transactions.efcte_dttm, 
       cab_cab_transactions.due_stlmnt_dt, 
       cab_cab_transactions.cncl_efcte_dttm, 
       cab_cab_transactions.trde_sstm_id, 
       cab_cab_transactions.trx_type_cd, 
       cab_cab_transactions.trx_type_dscrn, 
       cab_cab_transactions.trx_subtype_cd, 
       cab_cab_transactions.trde_stat_flg, 
       cab_cab_transactions.csh_cr_dr_indcr, 
       cab_cab_transactions.long_shrt_indcr, 
       cab_cab_transactions.lcl_crncy, 
       cab_cab_transactions.stlmt_crncy, 
       cab_cab_transactions.nomin_qty, 
       cab_cab_transactions.price, 
       cab_cab_transactions.lcl_cst, 
       cab_cab_transactions.prtfo_cst, 
       cab_cab_transactions.lcl_book_cst, 
       cab_cab_transactions.prtfo_book_cst, 
       cab_cab_transactions.lcl_sell_prcds, 
       cab_cab_transactions.prtfo_sell_prcds, 
       cab_cab_transactions.lcl_gnls, 
       cab_cab_transactions.prtfo_gnls, 
       cab_cab_transactions.lcl_acrd_intrt, 
       cab_cab_transactions.prtfo_acrd_intrt, 
       cab_cab_transactions.stlmt_crncy_stlmt_amt, 
       cab_cab_transactions.lcl_net_amt, 
       cab_cab_transactions.prtfo_net_amt, 
       cab_cab_transactions.fx_bght_amt, 
       cab_cab_transactions.fx_sold_amt, 
       cab_cab_transactions.prtfo_crncy_stlmt_amt, 
       cab_cab_transactions.prtfo_net_incme, 
       cab_cab_transactions.dvnd_crncy_net_incme, 
       cab_cab_transactions.dvnd_type_cd, 
       cab_cab_transactions.lcl_intrt_pd_rec, 
       cab_cab_transactions.prtfo_intrt_pd_rec, 
       cab_cab_transactions.lcl_dvdnd_pd_rec, 
       cab_cab_transactions.prtfo_dvdnd_pd_rec, 
       cab_cab_transactions.lcl_sundry_inc_pd_rec, 
       cab_cab_transactions.prtfo_sundry_inc_pd_rec, 
       cab_cab_transactions.bnk_csh_cptl_secid, 
       cab_cab_transactions.bnk_csh_inc_secid, 
       cab_cab_transactions.reportdate, 
       cab_cab_transactions.filename, 
        sysdate, 
       'e483448' 
   FROM cab_cfg_trx_type_mapping RIGHT JOIN(cab_cab_tran_adjustments 
      INNER JOIN cab_cab_transactions ON(cab_cab_transactions.branchcode = cab_cab_tran_adjustments.branchcode ) 
       AND(cab_cab_tran_adjustments.sstm_trx_id = cab_cab_transactions.sstm_trx_id)) ON(cab_cfg_trx_type_mapping.cab_trx_type_cd = cab_cab_transactions.trx_type_cd) 
       AND(nvl(cab_cfg_trx_type_mapping.cab_trx_subtype_cd,' ') = nvl(cab_cab_transactions.trx_subtype_cd,' ') 
       AND (cab_cfg_trx_type_mapping.branchcode=cab_cab_transactions.branchcode)) 
      WHERE cab_cab_transactions.prtfo_cd IN 
       (SELECT DISTINCT prtfo_cd 
        FROM cab_cab_valuations_working 
        WHERE created_by = 'e483448' 
          AND branchcode='ISA') 
       AND cab_cab_tran_adjustments.efcte_dttm > '2011-07-31' 
       AND cab_cab_tran_adjustments.efcte_dttm <= '2011-08-31' 
       AND eff_trde_stat_flg <> 'X' 
       AND cab_cab_transactions.branchcode = 'ISA' 
       AND cab_cab_tran_adjustments.branchcode = 'ISA' 
       AND(cab_cfg_trx_type_mapping.cab_reportgroup = 'CABValuation' OR cab_cfg_trx_type_mapping.cab_reportgroup IS NULL)

问题在distinct上面，它会导致对全表扫描，而且会导致排序，然后删除重复的记录，所以速度很慢，因此需要优化distinct。查了不少资料，并逐一尝试，最后发现了一个非常可观的优化结果，用group by。语句如下：

SELECT   'AMEND_NEW', 
       reporttitle, 
       reportsubtitle, 
       cab_cab_transactions.branchcode, 
       cab_cab_transactions.prtfo_cd, 
       cab_cab_transactions.sstm_scrty_id, 
       cab_cab_transactions.sstm_trx_id, 
       cab_cab_transactions.trde_dttm, 
       cab_cab_transactions.efcte_dttm, 
       cab_cab_transactions.due_stlmnt_dt, 
       cab_cab_transactions.cncl_efcte_dttm, 
       cab_cab_transactions.trde_sstm_id, 
       cab_cab_transactions.trx_type_cd, 
       cab_cab_transactions.trx_type_dscrn, 
       cab_cab_transactions.trx_subtype_cd, 
       cab_cab_transactions.trde_stat_flg, 
       cab_cab_transactions.csh_cr_dr_indcr, 
       cab_cab_transactions.long_shrt_indcr, 
       cab_cab_transactions.lcl_crncy, 
       cab_cab_transactions.stlmt_crncy, 
       cab_cab_transactions.nomin_qty, 
       cab_cab_transactions.price, 
       cab_cab_transactions.lcl_cst, 
       cab_cab_transactions.prtfo_cst, 
       cab_cab_transactions.lcl_book_cst, 
       cab_cab_transactions.prtfo_book_cst, 
       cab_cab_transactions.lcl_sell_prcds, 
       cab_cab_transactions.prtfo_sell_prcds, 
       cab_cab_transactions.lcl_gnls, 
       cab_cab_transactions.prtfo_gnls, 
       cab_cab_transactions.lcl_acrd_intrt, 
       cab_cab_transactions.prtfo_acrd_intrt, 
       cab_cab_transactions.stlmt_crncy_stlmt_amt, 
       cab_cab_transactions.lcl_net_amt, 
       cab_cab_transactions.prtfo_net_amt, 
       cab_cab_transactions.fx_bght_amt, 
       cab_cab_transactions.fx_sold_amt, 
       cab_cab_transactions.prtfo_crncy_stlmt_amt, 
       cab_cab_transactions.prtfo_net_incme, 
       cab_cab_transactions.dvnd_crncy_net_incme, 
       cab_cab_transactions.dvnd_type_cd, 
       cab_cab_transactions.lcl_intrt_pd_rec, 
       cab_cab_transactions.prtfo_intrt_pd_rec, 
       cab_cab_transactions.lcl_dvdnd_pd_rec, 
       cab_cab_transactions.prtfo_dvdnd_pd_rec, 
       cab_cab_transactions.lcl_sundry_inc_pd_rec, 
       cab_cab_transactions.prtfo_sundry_inc_pd_rec, 
       cab_cab_transactions.bnk_csh_cptl_secid, 
       cab_cab_transactions.bnk_csh_inc_secid, 
       cab_cab_transactions.reportdate, 
       cab_cab_transactions.filename, 
        sysdate, 
       'e483448' 
   FROM cab_cfg_trx_type_mapping RIGHT JOIN(cab_cab_tran_adjustments 
      INNER JOIN cab_cab_transactions ON(cab_cab_transactions.branchcode = cab_cab_tran_adjustments.branchcode ) 
       AND(cab_cab_tran_adjustments.sstm_trx_id = cab_cab_transactions.sstm_trx_id)) ON(cab_cfg_trx_type_mapping.cab_trx_type_cd = cab_cab_transactions.trx_type_cd) 
       AND(nvl(cab_cfg_trx_type_mapping.cab_trx_subtype_cd,' ') = nvl(cab_cab_transactions.trx_subtype_cd,' ') 
       AND (cab_cfg_trx_type_mapping.branchcode=cab_cab_transactions.branchcode)) 
      WHERE cab_cab_transactions.prtfo_cd IN 
       (SELECT DISTINCT prtfo_cd 
        FROM cab_cab_valuations_working 
        WHERE created_by = 'e483448' 
          AND branchcode='ISA') 
       AND cab_cab_tran_adjustments.efcte_dttm > '2011-07-31' 
       AND cab_cab_tran_adjustments.efcte_dttm <= '2011-08-31' 
       AND eff_trde_stat_flg <> 'X' 
       AND cab_cab_transactions.branchcode = 'ISA' 
       AND cab_cab_tran_adjustments.branchcode = 'ISA' 
       AND(cab_cfg_trx_type_mapping.cab_reportgroup = 'CABValuation' OR cab_cfg_trx_type_mapping.cab_reportgroup IS NULL) 
       GROUP BY  reporttitle, 
       reportsubtitle, 
       cab_cab_transactions.branchcode, 
       cab_cab_transactions.prtfo_cd, 
       cab_cab_transactions.sstm_scrty_id, 
       cab_cab_transactions.sstm_trx_id, 
       cab_cab_transactions.trde_dttm, 
       cab_cab_transactions.efcte_dttm, 
       cab_cab_transactions.due_stlmnt_dt, 
       cab_cab_transactions.cncl_efcte_dttm, 
       cab_cab_transactions.trde_sstm_id, 
       cab_cab_transactions.trx_type_cd, 
       cab_cab_transactions.trx_type_dscrn, 
       cab_cab_transactions.trx_subtype_cd, 
       cab_cab_transactions.trde_stat_flg, 
       cab_cab_transactions.csh_cr_dr_indcr, 
       cab_cab_transactions.long_shrt_indcr, 
       cab_cab_transactions.lcl_crncy, 
       cab_cab_transactions.stlmt_crncy, 
       cab_cab_transactions.nomin_qty, 
       cab_cab_transactions.price, 
       cab_cab_transactions.lcl_cst, 
       cab_cab_transactions.prtfo_cst, 
       cab_cab_transactions.lcl_book_cst, 
       cab_cab_transactions.prtfo_book_cst, 
       cab_cab_transactions.lcl_sell_prcds, 
       cab_cab_transactions.prtfo_sell_prcds, 
       cab_cab_transactions.lcl_gnls, 
       cab_cab_transactions.prtfo_gnls, 
       cab_cab_transactions.lcl_acrd_intrt, 
       cab_cab_transactions.prtfo_acrd_intrt, 
       cab_cab_transactions.stlmt_crncy_stlmt_amt, 
       cab_cab_transactions.lcl_net_amt, 
       cab_cab_transactions.prtfo_net_amt, 
       cab_cab_transactions.fx_bght_amt, 
       cab_cab_transactions.fx_sold_amt, 
       cab_cab_transactions.prtfo_crncy_stlmt_amt, 
       cab_cab_transactions.prtfo_net_incme, 
       cab_cab_transactions.dvnd_crncy_net_incme, 
       cab_cab_transactions.dvnd_type_cd, 
       cab_cab_transactions.lcl_intrt_pd_rec, 
       cab_cab_transactions.prtfo_intrt_pd_rec, 
       cab_cab_transactions.lcl_dvdnd_pd_rec, 
       cab_cab_transactions.prtfo_dvdnd_pd_rec, 
       cab_cab_transactions.lcl_sundry_inc_pd_rec, 
       cab_cab_transactions.prtfo_sundry_inc_pd_rec, 
       cab_cab_transactions.bnk_csh_cptl_secid, 
       cab_cab_transactions.bnk_csh_inc_secid, 
       cab_cab_transactions.reportdate, 
       cab_cab_transactions.filename

最后执行时间只有15.1秒，快了60多倍，不得不说这优化效果还是很可观的。不过查了很多资料，仍然没有发现合理地解释：为什么distinct 和group by的效率会有这么大差别。查的很多资料，讲的基本都是两者相差不大，实现也差不多。有待解决。

DISTINCT和GROUP BY这两者本质上应该没有可比性，distinct 取出唯一列，group by 是分组，但有时候在优化的时候，在没有聚合函数的时候，他们查出来的结果也一样。

标签：
oracle_distinct优化,group,by优化distinct,oracle_group_by优化
圆月山庄资源网 Design By www.vgjia.com
广告合作：本站广告合作请联系QQ：858582 申请时备注：广告合作（否则不回）
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
圆月山庄资源网 Design By www.vgjia.com
www.vgjia.com 圆月山庄资源网
139,976互联网资源
144,792高清电影
21,817无损音乐
631,128技术资源

更新日志

友情链接

杰晶网络 DDR爱好者之家 桃源资源网 杰网资源 富贵资源网 南强小屋 铁雪资源网 幽灵资源网 万梅资源网 狼山资源网 白云岛资源网 昆仑资源网 相思资源网 明霞山资源网 内蒙古资源网 黑松山资源网 茶园资源网 饿虎岗资源网 大旗谷资源网 常春岛资源网 岱庙资源网 兴国资源网 快活林资源网 蝙蝠岛资源网 帝王谷资源网 白云城资源网 伏龙阁资源网 清风细雨楼 天枫庄资源网 圆月山庄资源网 无争山庄资源网 神水资源网 移花宫资源网 神剑山庄资源网 无为清净楼资源网 金钱帮资源网 丐帮资源网 华山资源网 极乐门资源网 小李飞刀资源网 凤求凰客栈 风云阁资源网 金狮镖局 鸳鸯亭资源网 千金楼资源网 更多链接