数据库

oracle中使用group by优化distinct
数据库 2024/9/29 佚名
3 2

今天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优化
免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！ 如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com
白云城资源网 Copyright www.dyhadc.com

评论“oracle中使用group by优化distinct”

暂无“oracle中使用group by优化distinct”评论...
www.dyhadc.com 白云城资源网
129,905影音资源
244,626技术资源
111,817软件资源
578,645站长资源

《魔兽世界》大逃杀！60人新游玩模式《强袭风暴》3月21日上线

暴雪近日发布了《魔兽世界》10.2.6 更新内容，新游玩模式《强袭风暴》即将于3月21 日在亚服上线，届时玩家将前往阿拉希高地展开一场 60 人大逃杀对战。

艾泽拉斯的冒险者已经征服了艾泽拉斯的大地及遥远的彼岸。他们在对抗世界上最致命的敌人时展现出过人的手腕，并且成功阻止终结宇宙等级的威胁。当他们在为即将于《魔兽世界》资料片《地心之战》中来袭的萨拉塔斯势力做战斗准备时，他们还需要在熟悉的阿拉希高地面对一个全新的敌人──那就是彼此。在《巨龙崛起》10.2.6 更新的《强袭风暴》中，玩家将会进入一个全新的海盗主题大逃杀式限时活动，其中包含极高的风险和史诗级的奖励。

《强袭风暴》不是普通的战场，作为一个独立于主游戏之外的活动，玩家可以用大逃杀的风格来体验《魔兽世界》，不分职业、不分装备（除了你在赛局中捡到的），光是技巧和战略的强弱之分就能决定出谁才是能坚持到最后的赢家。本次活动将会开放单人和双人模式，玩家在加入海盗主题的预赛大厅区域前，可以从强袭风暴角色画面新增好友。游玩游戏将可以累计名望轨迹，《巨龙崛起》和《魔兽世界：巫妖王之怒 经典版》的玩家都可以获得奖励。

更新日志

友情链接

杰晶网络 DDR爱好者之家 南强小屋 黑松山资源网 白云城资源网 更多链接
白云城资源网 Copyright www.dyhadc.com