好得很程序员自学网

<tfoot draggable='sEl'></tfoot>

oracle中使用group by优化distinct

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

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

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。语句如下：

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

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

查看更多关于oracle中使用group by优化distinct的详细内容...

声明：本文来自网络，不代表【好得很程序员自学网】立场，转载请注明出处：http://www.haodehen.cn/did227718
  阅读：24次

上一篇： Oracle数据库中ORDER BY排序和查询按IN条件的顺序输出

下一篇：Mac上Oracle数据库的安装及过程中一些问题的解决

相关资讯