天枫庄资源网 Design By www.wosibo.com
今天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 是分组,但有时候在优化的时候,在没有聚合函数的时候,他们查出来的结果也一样。
天枫庄资源网 Design By www.wosibo.com
广告合作:本站广告合作请联系QQ:858582 申请时备注:广告合作(否则不回)
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件! 如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
天枫庄资源网 Design By www.wosibo.com
暂无oracle中使用group by优化distinct的评论...
稳了!魔兽国服回归的3条重磅消息!官宣时间再确认!
昨天有一位朋友在大神群里分享,自己亚服账号被封号之后居然弹出了国服的封号信息对话框。
这里面让他访问的是一个国服的战网网址,com.cn和后面的zh都非常明白地表明这就是国服战网。
而他在复制这个网址并且进行登录之后,确实是网易的网址,也就是我们熟悉的停服之后国服发布的暴雪游戏产品运营到期开放退款的说明。这是一件比较奇怪的事情,因为以前都没有出现这样的情况,现在突然提示跳转到国服战网的网址,是不是说明了简体中文客户端已经开始进行更新了呢?
更新日志
2024年10月22日
2024年10月22日
- 群星2013-青春缤纷辑压箱宝大公开3CD2[新加坡限量版][WAV整轨]
- 林育群.2013-BalladShow(日本版)【环球】【WAV+CUE】
- 陈加洛.1992-痛到感觉不到【宝丽金】【WAV+CUE】
- 群星.2023-宿命之敌电视剧原声带【韶愔音乐】【FLAC分轨】
- 東京事変-大発見[FLAC+CUE]
- 椎名林檎-三文ゴシップ[FLAC+CUE]
- 2024年08月04日
- 裘德《裘德「最后的水族馆」演唱会LIVE》[320K/MP3][228.89MB]
- 裘德《裘德「最后的水族馆」演唱会LIVE》[24bit 48kHz][FLAC/分轨][2.08G]
- 基因三重奏《如果你什么都不说 音乐会现场录音》[320K/MP3][145.37MB]
- 孟庭苇.1996-月亮说话(2020环球24KGOLD限量版)【上华】【WAV+CUE】
- 群星.1997-新艺宝优质音响系列·国语精选监听版【新艺宝】【WAV+CUE】
- 阿桑.2005-寂寞在唱歌(星外星引进版)【华研国际】【WAV+CUE】
- 基因三重奏《如果你什么都不说 音乐会现场录音》[FLAC/分轨][287.43MB]
- 蔡题谦《我爱你,却依然要看你走》[320K/MP3][88.65MB]