正文

oracle中使用group by优化distinct

︶ㄣ米米素材网 メ
米米素材网V管理员 / 阅读
0919

这篇文章主要介绍了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 是分组，但有时候在优化的时候，在没有聚合函数的时候，他们查出来的结果也一样。


注：相关教程知识阅读请移步到oracle教程频道。