计算机教程网

请输入关键字词
您现在的位置是：首页 > 主机教程 > 数据库技术

数据库技术

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

2024-11-04 20:03:13数据库技术 主机评测网
这篇文章主要给大家介绍了关于如何利用分析函数改写范围判断自关联查询的相关资料，文中通过示例代码介绍的非常详细，对大家学习或者使用sql具有一定的参考学习价值，需要的朋友们下面随着小编来一起学习学习吧

前言

最近碰到一个单条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

赞一个！ ()

相关文章