加入收藏 | 设为首页 | 会员中心 | 我要投稿 淮南站长网 (https://www.0554zz.cn/)- 管理运维、图像技术、智能营销、专属主机、5G!
当前位置: 首页 > 教程 > 正文

oracle中使用group by优化distinct

发布时间:2022-08-05 09:58:54 所属栏目:教程 来源:互联网
导读:这篇文章主要介绍了oracle中使用group by优化distinct的相关资料,需要的朋友可以参考下 今天mentor给了一个sql语句优化的任务。(环境是sql developer)有一个语句执行很慢,查询出来的结果有17544条记录,但需970秒,速度很慢。语句是这样的: SELECT DISTINC
  这篇文章主要介绍了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,  

(编辑:淮南站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读