這篇文章主要介紹了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 是分組，但有時候在優化的時候，在沒有聚合函數的時候，他們查出來的結果也一樣。