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⑶1’
AND cab_cab_tran_adjustments.efcte_dttm <= ‘2011-08⑶1’
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⑶1’
AND cab_cab_tran_adjustments.efcte_dttm <= ‘2011-08⑶1’
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中使用group by优化distinct
https://www.wanzijz.com/view/34293.html
