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

前言

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