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

为正

前言

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