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

前言

最近碰到一个单条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文本类似如下：

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

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

总结

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