如何利用分析函数改写范围判断自关联查询详解

前言 最近碰到一个单条SQL运行效率不佳导致数据库整体运行负载较高的问题。 分析、定位数据库的主要负载是这条语句引起的过程相对简单，通过AWR报告就可以比较容易的完成定位，这里就不赘述了。 现在直接看一下这个导致性能问题的SQL语句，其对应的SQL REPORT统计如下： Stat Name Statement Total Per Execution 0 Comments

前言

最近碰到一个单条SQL运行效率不佳导致数据库整体运行负载较高的问题。

分析、定位数据库的主要负载是这条语句引起的过程相对简单，通过AWR报告就可以比较容易的完成定位，这里就不赘述了。

现在直接看一下这个导致性能问题的SQL语句，其对应的SQL REPORT统计如下：

Stat Name Statement Total Per Execution % Snap Total
Elapsed Time (ms) 363,741 363,740.78 8 .42
CPU Time (ms) 362,770 362,770.00 8 .81
Executions 1
Buffer Gets 756 756.00 0.00
Disk Reads 0 0.00 0.00
Parse Calls 1 1.00 0.01
Rows 50,825 50,825.00
User I/O Wait Time (ms) 0
Cluster Wait Time (ms) 0
Application Wait Time (ms) 0
Concurrency Wait Time (ms) 0
Invalidations 0
Version Count 1
Sharable Mem(KB) 28

从SQL的性能指标上看，其单次执行需要6分钟左右，处理5万多条记录，逻辑度只有756，主要消耗时间在CPU上。而这里就存在疑点，逻辑读如此之低，而CPU时间花费又如此之高，那么这些CPU都消耗在哪里呢？当然这个问通过SQL的统计信息中是找不到答案的，我们下面关注SQL的执行计划：

Id Operation Name Rows Bytes TempSpc Cost (%CPU) Time
0 SELECT STATEMENT 1226 (100)
1 SORT ORDER BY 49379 3375K 3888K 1226 (2) 00:00:05
2 HASH JOIN ANTI 49379 3375K 2272K 401 (3) 00:00:02
3 TABLE ACCESS FULL T_NUM 49379 1687K 88 (4) 00:00:01
4 TABLE ACCESS FULL T_NUM 49379 1687K 88 (4) 00:00:01

从执行计划看，Oracle选择了HASH JOIN ANTI，JOIN的两张表都是T_NUM，且都采用了全表扫描，并未选择索引。仅靠执行计划也只等得到上面的结论，至于为什么不选择索引，以及为什么执行时间过长，还需要进一步的分析。

将原SQL进行简单脱密改写后， SQL文本类似如下：

?

1

2

3

4

5

6

7

8

9

SELECT BEGIN, END, ROWID, LENGTH(BEGIN)

FROM T_NUM A

WHERE NOT EXISTS (

SELECT 1

FROM T_NUM B

WHERE B.BEGIN <= A.BEGIN

AND B.END >= A.END

AND B.ROWID != A.ROWID

AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN));

如果分析SQL语句，会发现这是一个自关联语句，在BEGIN字段长度相等的前提下，想要找到哪些不存在BEGIN比当前记录BEGIN小且END比当前记录END大的记录。

简单一点说，表中的记录表示的是由BEGIN开始到END截至的范围，那么当前想要获取的结果是找出哪些没有范围所包含的范围。需要注意的是，对于当前的SQL逻辑，如果存在两条范围完全相同的记录，那么最终这两条记录都会被舍弃。

业务的逻辑并不是特别复杂，但是要解决一条记录与其他记录进行比较，多半采用的方法是自关联，而在这个自关联中，既有大于等于又有小于等于，还有不等于，仅有的一个等于的关联条件，来自范围段BEGIN的长度的比较。

显而易见的是，如果是范围段本身的比较，其选择度一般还是不错的，但是如果只是比较其长度，那么无疑容易产生大量的重复，比如在这个例子中：

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

SQL> select length(begin), count(*) from t_num group by length(begin) order by 2 desc;

LENGTH(BEGIN) COUNT(*)

————- ———-

12 22096

11 9011

13 8999

14 8186

16 49

9 45

8 41

7 27

大量重复的数据出现在长度为11到14的范围上，在这种情况下，仅有的一个等值判断条件LENGTH(BEGIN)是非常低效的，这时一条记录根据这个等值条件会关联到近万条记录，设置关联到两万多条记录，显然大量的实践消耗在低效的连接过程中。

再来看一下具体的SQL语句，会发现几乎没有办法建立索引，因为LENGTH(BEGIN)的选择度非常查，而其他的条件都是不等查询，选择度也不会好，即使建立索引，强制执行选择索引，效率也不会好。

那么如果想要继续优化这个SQL，就只剩下一个办法，那就是SQL的改写。对于自关联查询而言，最佳的改写方法是利用分析函数，其强大的行级处理能力，可以在一次扫描过程中获得一条记录与其他记录的关系，从而消除了自关联的必要性。

SQL改写结果如下：

?

1

2

3

4

5

6

7

8

9

10

11

12

SELECT BEGIN, OLDEND END, LENGTH(BEGIN)

FROM (

SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,

ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN

FROM

(

SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END

FROM T_NUM

)

)

WHERE RN = 1

AND CN = 1;

简单的说，内层的分析函数MAX用来根据BEGIN从小到大，END从大到小的条件，确定每个范围对应的最大的END的值。而外层的两个分析函数，COUNT用来去掉完全重复的记录，而ROW_NUMBER用来获取范围最大的记录（也就是没有被其他记录的范围所涵盖）。

改写后，这个SQL避免对自关联，也就不存在关联条件重复值过高的性能隐患了。在模拟环境中，性能对比如下：

?

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

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

SQL> SELECT BEGIN, END, ROWID, LENGTH(BEGIN)

2 FROM T_NUM A

3 WHERE NOT EXISTS (

4 SELECT 1

5 FROM T_NUM B

6 WHERE B.BEGIN <= A.BEGIN

7 AND B.END >= A.END

8 AND B.ROWID != A.ROWID

9 AND LENGTH(B.BEGIN) = LENGTH(A.BEGIN))

10 ;

48344 rows selected.

Elapsed: 00:00:57.68

Execution Plan

———————————————————-

Plan hash value: 2540751655

————————————————————————————

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

————————————————————————————

| 0 | SELECT STATEMENT | | 48454 | 1703K| | 275 (1)| 00:00:04 |

|* 1 | HASH JOIN ANTI | | 48454 | 1703K| 1424K| 275 (1)| 00:00:04 |

| 2 | TABLE ACCESS FULL| T_NUM | 48454 | 851K| | 68 (0)| 00:00:01 |

| 3 | TABLE ACCESS FULL| T_NUM | 48454 | 851K| | 68 (0)| 00:00:01 |

————————————————————————————

Predicate Information (identified by operation id):

—————————————————

1 – access(LENGTH(TO_CHAR(“B”.”BEGIN”))=LENGTH(TO_CHAR(“A”.”BEGIN”)))

filter(“B”.”BEGIN”<=”A”.”BEGIN” AND “B”.”END”>=”A”.”END” AND

“B”.ROWID<>”A”.ROWID)

Statistics

———————————————————-

0 recursive calls

0 db block gets

404 consistent gets

0 physical reads

0 redo size

2315794 bytes sent via SQL*Net to client

35966 bytes received via SQL*Net from client

3224 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

48344 rows processed

SQL> SELECT BEGIN, OLDEND END, LENGTH(BEGIN)

2 FROM (

3 SELECT BEGIN, OLDEND, END, LENGTH(BEGIN), COUNT(*) OVER(PARTITION BY LENGTH(BEGIN), BEGIN, OLDEND) CN,

4 ROW_NUMBER() OVER(PARTITION BY LENGTH(BEGIN), END ORDER BY BEGIN) RN

5 FROM

6 (

7 SELECT BEGIN, END OLDEND, MAX(END) OVER(PARTITION BY LENGTH(BEGIN) ORDER BY BEGIN, END DESC) END

8 FROM T_NUM

9 )

10 )

11 WHERE RN = 1

12 AND CN = 1;

48344 rows selected.

Elapsed: 00:00:00.72

Execution Plan

———————————————————-

Plan hash value: 1546715670

——————————————————————————————

| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |

——————————————————————————————

| 0 | SELECT STATEMENT | | 48454 | 2460K| | 800 (1)| 00:00:10 |

|* 1 | VIEW | | 48454 | 2460K| | 800 (1)| 00:00:10 |

|* 2 | WINDOW SORT PUSHED RANK| | 48454 | 1845K| 2480K| 800 (1)| 00:00:10 |

| 3 | WINDOW BUFFER | | 48454 | 1845K| | 800 (1)| 00:00:10 |

| 4 | VIEW | | 48454 | 1845K| | 311 (1)| 00:00:04 |

| 5 | WINDOW SORT | | 48454 | 662K| 1152K| 311 (1)| 00:00:04 |

| 6 | TABLE ACCESS FULL | T_NUM | 48454 | 662K| | 68 (0)| 00:00:01 |

——————————————————————————————

Predicate Information (identified by operation id):

—————————————————

1 – filter(“RN”=1 AND “CN”=1)

2 – filter(ROW_NUMBER() OVER ( PARTITION BY LENGTH(TO_CHAR(“BEGIN”)),”END”

ORDER BY “BEGIN”)<=1)

Statistics

———————————————————-

0 recursive calls

0 db block gets

202 consistent gets

0 physical reads

0 redo size

1493879 bytes sent via SQL*Net to client

35966 bytes received via SQL*Net from client

3224 SQL*Net roundtrips to/from client

3 sorts (memory)

0 sorts (disk)

48344 rows processed

原SQL运行时间接近1分钟，而改写后的SQL语句只需要0.72秒，执行时间变为原本的1/80，逻辑读减少一半。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对服务器之家的支持。

原文链接：http://yangtingkun.net/?p=1513

本文链接：https://my.lmcjl.com/post/12458.html

展开阅读全文

相关文章

4 评论

留下您的评论.