最新公告
  • 欢迎您光临优源网,本站秉承服务宗旨 履行“站长”责任,销售只是起点 服务永无止境!立即加入我们
  • oracle中使用group by优化distinct

    这篇文章主要介绍了oracle中使用group by优化distinct的相关资料,需要的朋友可以参考下

    今天mentor给了一个sql语句优化的任务。(环境是sql developer)有一个语句执行很慢,查询出来的结果有17544条记录,但需970秒,速度很慢。语句是这样的:

     

     

    1. SELECT DISTINCT ‘AMEND_NEW’,
    2. reporttitle,
    3. reportsubtitle,
    4. cab_cab_transactions.branchcode,
    5. cab_cab_transactions.prtfo_cd,
    6. cab_cab_transactions.sstm_scrty_id,
    7. cab_cab_transactions.sstm_trx_id,
    8. cab_cab_transactions.trde_dttm,
    9. cab_cab_transactions.efcte_dttm,
    10. cab_cab_transactions.due_stlmnt_dt,
    11. cab_cab_transactions.cncl_efcte_dttm,
    12. cab_cab_transactions.trde_sstm_id,
    13. cab_cab_transactions.trx_type_cd,
    14. cab_cab_transactions.trx_type_dscrn,
    15. cab_cab_transactions.trx_subtype_cd,
    16. cab_cab_transactions.trde_stat_flg,
    17. cab_cab_transactions.csh_cr_dr_indcr,
    18. cab_cab_transactions.long_shrt_indcr,
    19. cab_cab_transactions.lcl_crncy,
    20. cab_cab_transactions.stlmt_crncy,
    21. cab_cab_transactions.nomin_qty,
    22. cab_cab_transactions.price,
    23. cab_cab_transactions.lcl_cst,
    24. cab_cab_transactions.prtfo_cst,
    25. cab_cab_transactions.lcl_book_cst,
    26. cab_cab_transactions.prtfo_book_cst,
    27. cab_cab_transactions.lcl_sell_prcds,
    28. cab_cab_transactions.prtfo_sell_prcds,
    29. cab_cab_transactions.lcl_gnls,
    30. cab_cab_transactions.prtfo_gnls,
    31. cab_cab_transactions.lcl_acrd_intrt,
    32. cab_cab_transactions.prtfo_acrd_intrt,
    33. cab_cab_transactions.stlmt_crncy_stlmt_amt,
    34. cab_cab_transactions.lcl_net_amt,
    35. cab_cab_transactions.prtfo_net_amt,
    36. cab_cab_transactions.fx_bght_amt,
    37. cab_cab_transactions.fx_sold_amt,
    38. cab_cab_transactions.prtfo_crncy_stlmt_amt,
    39. cab_cab_transactions.prtfo_net_incme,
    40. cab_cab_transactions.dvnd_crncy_net_incme,
    41. cab_cab_transactions.dvnd_type_cd,
    42. cab_cab_transactions.lcl_intrt_pd_rec,
    43. cab_cab_transactions.prtfo_intrt_pd_rec,
    44. cab_cab_transactions.lcl_dvdnd_pd_rec,
    45. cab_cab_transactions.prtfo_dvdnd_pd_rec,
    46. cab_cab_transactions.lcl_sundry_inc_pd_rec,
    47. cab_cab_transactions.prtfo_sundry_inc_pd_rec,
    48. cab_cab_transactions.bnk_csh_cptl_secid,
    49. cab_cab_transactions.bnk_csh_inc_secid,
    50. cab_cab_transactions.reportdate,
    51. cab_cab_transactions.filename,
    52. sysdate,
    53. ‘e483448’
    54. FROM cab_cfg_trx_type_mapping RIGHT JOIN(cab_cab_tran_adjustments
    55. INNER JOIN cab_cab_transactions ON(cab_cab_transactions.branchcode = cab_cab_tran_adjustments.branchcode )
    56. 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)
    57. AND(nvl(cab_cfg_trx_type_mapping.cab_trx_subtype_cd,’ ‘) = nvl(cab_cab_transactions.trx_subtype_cd,’ ‘)
    58. AND (cab_cfg_trx_type_mapping.branchcode=cab_cab_transactions.branchcode))
    59. WHERE cab_cab_transactions.prtfo_cd IN
    60. (SELECT DISTINCT prtfo_cd
    61. FROM cab_cab_valuations_working
    62. WHERE created_by = ‘e483448’
    63. AND branchcode=’ISA’)
    64. AND cab_cab_tran_adjustments.efcte_dttm > ‘2011-07-31’
    65. AND cab_cab_tran_adjustments.efcte_dttm <= ‘2011-08-31’
    66. AND eff_trde_stat_flg <> ‘X’
    67. AND cab_cab_transactions.branchcode = ‘ISA’
    68. AND cab_cab_tran_adjustments.branchcode = ‘ISA’
    69. AND(cab_cfg_trx_type_mapping.cab_reportgroup = ‘CABValuation’ OR cab_cfg_trx_type_mapping.cab_reportgroup IS NULL)

    问题在distinct上面,它会导致对全表扫描,而且会导致排序,然后删除重复的记录,所以速度很慢,因此需要优化distinct。查了不少资料,并逐一尝试,最后发现了一个非常可观的优化结果,用group by。语句如下:

     

     

    1. SELECT ‘AMEND_NEW’,
    2. reporttitle,
    3. reportsubtitle,
    4. cab_cab_transactions.branchcode,
    5. cab_cab_transactions.prtfo_cd,
    6. cab_cab_transactions.sstm_scrty_id,
    7. cab_cab_transactions.sstm_trx_id,
    8. cab_cab_transactions.trde_dttm,
    9. cab_cab_transactions.efcte_dttm,
    10. cab_cab_transactions.due_stlmnt_dt,
    11. cab_cab_transactions.cncl_efcte_dttm,
    12. cab_cab_transactions.trde_sstm_id,
    13. cab_cab_transactions.trx_type_cd,
    14. cab_cab_transactions.trx_type_dscrn,
    15. cab_cab_transactions.trx_subtype_cd,
    16. cab_cab_transactions.trde_stat_flg,
    17. cab_cab_transactions.csh_cr_dr_indcr,
    18. cab_cab_transactions.long_shrt_indcr,
    19. cab_cab_transactions.lcl_crncy,
    20. cab_cab_transactions.stlmt_crncy,
    21. cab_cab_transactions.nomin_qty,
    22. cab_cab_transactions.price,
    23. cab_cab_transactions.lcl_cst,
    24. cab_cab_transactions.prtfo_cst,
    25. cab_cab_transactions.lcl_book_cst,
    26. cab_cab_transactions.prtfo_book_cst,
    27. cab_cab_transactions.lcl_sell_prcds,
    28. cab_cab_transactions.prtfo_sell_prcds,
    29. cab_cab_transactions.lcl_gnls,
    30. cab_cab_transactions.prtfo_gnls,
    31. cab_cab_transactions.lcl_acrd_intrt,
    32. cab_cab_transactions.prtfo_acrd_intrt,
    33. cab_cab_transactions.stlmt_crncy_stlmt_amt,
    34. cab_cab_transactions.lcl_net_amt,
    35. cab_cab_transactions.prtfo_net_amt,
    36. cab_cab_transactions.fx_bght_amt,
    37. cab_cab_transactions.fx_sold_amt,
    38. cab_cab_transactions.prtfo_crncy_stlmt_amt,
    39. cab_cab_transactions.prtfo_net_incme,
    40. cab_cab_transactions.dvnd_crncy_net_incme,
    41. cab_cab_transactions.dvnd_type_cd,
    42. cab_cab_transactions.lcl_intrt_pd_rec,
    43. cab_cab_transactions.prtfo_intrt_pd_rec,
    44. cab_cab_transactions.lcl_dvdnd_pd_rec,
    45. cab_cab_transactions.prtfo_dvdnd_pd_rec,
    46. cab_cab_transactions.lcl_sundry_inc_pd_rec,
    47. cab_cab_transactions.prtfo_sundry_inc_pd_rec,
    48. cab_cab_transactions.bnk_csh_cptl_secid,
    49. cab_cab_transactions.bnk_csh_inc_secid,
    50. cab_cab_transactions.reportdate,
    51. cab_cab_transactions.filename,
    52. sysdate,
    53. ‘e483448’
    54. FROM cab_cfg_trx_type_mapping RIGHT JOIN(cab_cab_tran_adjustments
    55. INNER JOIN cab_cab_transactions ON(cab_cab_transactions.branchcode = cab_cab_tran_adjustments.branchcode )
    56. 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)
    57. AND(nvl(cab_cfg_trx_type_mapping.cab_trx_subtype_cd,’ ‘) = nvl(cab_cab_transactions.trx_subtype_cd,’ ‘)
    58. AND (cab_cfg_trx_type_mapping.branchcode=cab_cab_transactions.branchcode))
    59. WHERE cab_cab_transactions.prtfo_cd IN
    60. (SELECT DISTINCT prtfo_cd
    61. FROM cab_cab_valuations_working
    62. WHERE created_by = ‘e483448’
    63. AND branchcode=’ISA’)
    64. AND cab_cab_tran_adjustments.efcte_dttm > ‘2011-07-31’
    65. AND cab_cab_tran_adjustments.efcte_dttm <= ‘2011-08-31’
    66. AND eff_trde_stat_flg <> ‘X’
    67. AND cab_cab_transactions.branchcode = ‘ISA’
    68. AND cab_cab_tran_adjustments.branchcode = ‘ISA’
    69. AND(cab_cfg_trx_type_mapping.cab_reportgroup = ‘CABValuation’ OR cab_cfg_trx_type_mapping.cab_reportgroup IS NULL)
    70. GROUP BY reporttitle,
    71. reportsubtitle,
    72. cab_cab_transactions.branchcode,
    73. cab_cab_transactions.prtfo_cd,
    74. cab_cab_transactions.sstm_scrty_id,
    75. cab_cab_transactions.sstm_trx_id,
    76. cab_cab_transactions.trde_dttm,
    77. cab_cab_transactions.efcte_dttm,
    78. cab_cab_transactions.due_stlmnt_dt,
    79. cab_cab_transactions.cncl_efcte_dttm,
    80. cab_cab_transactions.trde_sstm_id,
    81. cab_cab_transactions.trx_type_cd,
    82. cab_cab_transactions.trx_type_dscrn,
    83. cab_cab_transactions.trx_subtype_cd,
    84. cab_cab_transactions.trde_stat_flg,
    85. cab_cab_transactions.csh_cr_dr_indcr,
    86. cab_cab_transactions.long_shrt_indcr,
    87. cab_cab_transactions.lcl_crncy,
    88. cab_cab_transactions.stlmt_crncy,
    89. cab_cab_transactions.nomin_qty,
    90. cab_cab_transactions.price,
    91. cab_cab_transactions.lcl_cst,
    92. cab_cab_transactions.prtfo_cst,
    93. cab_cab_transactions.lcl_book_cst,
    94. cab_cab_transactions.prtfo_book_cst,
    95. cab_cab_transactions.lcl_sell_prcds,
    96. cab_cab_transactions.prtfo_sell_prcds,
    97. cab_cab_transactions.lcl_gnls,
    98. cab_cab_transactions.prtfo_gnls,
    99. cab_cab_transactions.lcl_acrd_intrt,
    100. cab_cab_transactions.prtfo_acrd_intrt,
    101. cab_cab_transactions.stlmt_crncy_stlmt_amt,
    102. cab_cab_transactions.lcl_net_amt,
    103. cab_cab_transactions.prtfo_net_amt,
    104. cab_cab_transactions.fx_bght_amt,
    105. cab_cab_transactions.fx_sold_amt,
    106. cab_cab_transactions.prtfo_crncy_stlmt_amt,
    107. cab_cab_transactions.prtfo_net_incme,
    108. cab_cab_transactions.dvnd_crncy_net_incme,
    109. cab_cab_transactions.dvnd_type_cd,
    110. cab_cab_transactions.lcl_intrt_pd_rec,
    111. cab_cab_transactions.prtfo_intrt_pd_rec,
    112. cab_cab_transactions.lcl_dvdnd_pd_rec,
    113. cab_cab_transactions.prtfo_dvdnd_pd_rec,
    114. cab_cab_transactions.lcl_sundry_inc_pd_rec,
    115. cab_cab_transactions.prtfo_sundry_inc_pd_rec,
    116. cab_cab_transactions.bnk_csh_cptl_secid,
    117. cab_cab_transactions.bnk_csh_inc_secid,
    118. cab_cab_transactions.reportdate,
    119. cab_cab_transactions.filename

    最后执行时间只有15.1秒,快了60多倍,不得不说这优化效果还是很可观的。不过查了很多资料,仍然没有发现合理地解释:为什么distinct 和group by的效率会有这么大差别。查的很多资料,讲的基本都是两者相差不大,实现也差不多。有待解决。

    DISTINCT和GROUP BY这两者本质上应该没有可比性,distinct 取出唯一列,group by 是分组,但有时候在优化的时候,在没有聚合函数的时候,他们查出来的结果也一样。

    1. 本站所有资源来源于用户上传和网络,因此不包含技术服务请大家谅解!如有侵权请邮件联系客服!lyebiz@qq.com
    2. 本站不保证所提供下载的资源的准确性、安全性和完整性,资源仅供下载学习之用!如有链接无法下载、失效或广告,请联系客服处理,有奖励!
    3. 您必须在下载后的24个小时之内,从您的电脑中彻底删除上述内容资源!如用于商业或者非法用途,与本站无关,一切后果请用户自负!
    4. 如果您也有好的资源或教程,您可以投稿发布,成功分享后有站币奖励和额外收入!

    优源网 » oracle中使用group by优化distinct

    常见问题FAQ

    免费下载或者VIP会员专享资源能否直接商用?
    本站所有资源版权均属于原作者所有,这里所提供资源均只能用于参考学习用,请勿直接商用。若由于商用引起版权纠纷,一切责任均由使用者承担。更多说明请参考 VIP介绍。
    提示下载完但解压或打开不了?
    最常见的情况是下载不完整: 可对比下载完压缩包的与网盘上的容量,若小于网盘提示的容量则是这个原因。这是浏览器下载的bug,建议用百度网盘软件或迅雷下载。若排除这种情况,可在对应资源底部留言,或 联络我们.。
    找不到素材资源介绍文章里的示例图片?
    对于PPT,KEY,Mockups,APP,网页模版等类型的素材,文章内用于介绍的图片通常并不包含在对应可供下载素材包内。这些相关商业图片需另外购买,且本站不负责(也没有办法)找到出处。 同样地一些字体文件也是这种情况,但部分素材会在素材包内有一份字体下载链接清单。
    资源不能下载?
    如果遇到链接失效不能下载,请立即联系客服发货!
    • 406会员总数(位)
    • 169资源总数(个)
    • 21本周发布(个)
    • 0 今日发布(个)
    • 73稳定运行(天)

    提供最优质的资源集合

    立即查看 了解详情