怎么利用分析函数改写范围判断自关联查询

共计 5567 个字符，预计需要花费 14 分钟才能阅读完成。

自动写代码机器人，免费开通

丸趣 TV 小编给大家分享一下怎么利用分析函数改写范围判断自关联查询，希望大家阅读完这篇文章之后都有所收获，下面让我们一起去探讨吧！

前言

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

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

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

Stat NameStatement TotalPer Execution% Snap TotalElapsed Time (ms)363,741363,740.788 .42CPU Time (ms)362,770362,770.008 .81Executions1 Buffer Gets756756.000.00Disk Reads00.000.00Parse Calls11.000.01Rows50,82550,825.00 User I/O Wait Time (ms)0 
Cluster Wait Time (ms)0 Application Wait Time (ms)0 Concurrency Wait Time (ms)0 Invalidations0 Version Count1 Sharable Mem(KB)28 

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

IdOperationNameRowsBytesTempSpcCost (%CPU)Time0SELECT STATEMENT 
1226 (100) 1  SORT ORDER BY 493793375K3888K1226 (2)00:00:052  HASH JOIN ANTI 493793375K2272K401 (3)00:00:023  TABLE ACCESS FULLT_NUM493791687K 88 (4)00:00:014  TABLE ACCESS FULLT_NUM493791687K 88 (4)00:00:01

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

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

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 的长度的比较。

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

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 改写结果如下：

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
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 避免对自关联，也就不存在关联条件重复值过高的性能隐患了。在模拟环境中，性能对比如下：

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，逻辑读减少一半。

看完了这篇文章，相信你对“怎么利用分析函数改写范围判断自关联查询”有了一定的了解，如果想了解更多相关知识，欢迎关注丸趣 TV 行业资讯频道，感谢各位的阅读！

向 AI 问一下细节