45fan.com - 路饭网

搜索: 您的位置主页 > 网络频道 > 阅读资讯:如何在oracle中使用group by优化distinct?

如何在oracle中使用group by优化distinct?

2016-03-03 16:26:00 来源:www.45fan.com 【

如何在oracle中使用group by优化distinct?

今天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 是分组,但有时候在优化的时候,在没有聚合函数的时候,他们查出来的结果也一样。


本文地址:http://www.45fan.com/a/question/41107.html
Tags: oracle 优化 group
编辑:路饭网
关于我们 | 联系我们 | 友情链接 | 网站地图 | Sitemap | App | 返回顶部