oracle中使用group by优化distinct
今天mentor给了一个sql语句优化的任务。（环境是sqldeveloper）有一个语句执行很慢，查询出来的结果有17544条记录，但需970秒，速度很慢。语句是这样的：
SELECTDISTINCT'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' FROMcab_cfg_trx_type_mappingRIGHTJOIN(cab_cab_tran_adjustments INNERJOINcab_cab_transactionsON(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)) WHEREcab_cab_transactions.prtfo_cdIN (SELECTDISTINCTprtfo_cd FROMcab_cab_valuations_working WHEREcreated_by='e483448' ANDbranchcode='ISA') ANDcab_cab_tran_adjustments.efcte_dttm>'2011-07-31' ANDcab_cab_tran_adjustments.efcte_dttm<='2011-08-31' ANDeff_trde_stat_flg<>'X' ANDcab_cab_transactions.branchcode='ISA' ANDcab_cab_tran_adjustments.branchcode='ISA' AND(cab_cfg_trx_type_mapping.cab_reportgroup='CABValuation'ORcab_cfg_trx_type_mapping.cab_reportgroupISNULL)
问题在distinct上面，它会导致对全表扫描，而且会导致排序，然后删除重复的记录，所以速度很慢，因此需要优化distinct。查了不少资料，并逐一尝试，最后发现了一个非常可观的优化结果，用groupby。语句如下：
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' FROMcab_cfg_trx_type_mappingRIGHTJOIN(cab_cab_tran_adjustments INNERJOINcab_cab_transactionsON(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)) WHEREcab_cab_transactions.prtfo_cdIN (SELECTDISTINCTprtfo_cd FROMcab_cab_valuations_working WHEREcreated_by='e483448' ANDbranchcode='ISA') ANDcab_cab_tran_adjustments.efcte_dttm>'2011-07-31' ANDcab_cab_tran_adjustments.efcte_dttm<='2011-08-31' ANDeff_trde_stat_flg<>'X' ANDcab_cab_transactions.branchcode='ISA' ANDcab_cab_tran_adjustments.branchcode='ISA' AND(cab_cfg_trx_type_mapping.cab_reportgroup='CABValuation'ORcab_cfg_trx_type_mapping.cab_reportgroupISNULL) GROUPBYreporttitle, 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和groupby的效率会有这么大差别。查的很多资料，讲的基本都是两者相差不大，实现也差不多。有待解决。
DISTINCT和GROUPBY这两者本质上应该没有可比性，distinct取出唯一列，groupby是分组，但有时候在优化的时候，在没有聚合函数的时候，他们查出来的结果也一样。