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

前言

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

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

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

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

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

IdOperationNameRowsBytesTempSpcCost (%CPU)Time0SELECT STATEMENT
1226 (100)1 SORT ORDER BY493793375K3888K1226 (2)00:00:052 HASH JOIN ANTI493793375K2272K401 (3)00:00:023 TABLE ACCESS FULLT_NUM493791687K88 (4)00:00:014 TABLE ACCESS FULLT_NUM493791687K88 (4)00:00:01

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

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

SELECTBEGIN,END,ROWID,LENGTH(BEGIN)FROMT_NUMAWHERENOTEXISTS(SELECT1FROMT_NUMBWHEREB.BEGIN<=A.BEGINANDB.END>=A.ENDANDB.ROWID!=A.ROWIDANDLENGTH(B.BEGIN)=LENGTH(A.BEGIN));

如果分析SQL语句，会发现这是一个自关联语句，在BEGIN字段长度相等的前提下，想要找到哪些不存在BEGIN比当前记录BEGIN小且END比当前记录END大的记录。

简单一点说，表中的记录表示的是由BEGIN开始到END截至的范围，那么当前想要获取的结果是找出哪些没有范围所包含的范围。需要注意的是，对于当前的SQL逻辑，如果存在两条范围完全相同的记录，那么最终这两条记录都会被舍弃。

业务的逻辑并不是特别复杂，但是要解决一条记录与其他记录进行比较，多半采用的方法是自关联，而在这个自关联中，既有大于等于又有小于等于，还有不等于，仅有的一个等于的关联条件，来自范围段BEGIN的长度的比较。

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

SQL>selectlength(begin),count(*)fromt_numgroupbylength(begin)orderby2desc;LENGTH(BEGIN)COUNT(*)————-———-12220961190111389991481861649945841727

大量重复的数据出现在长度为11到14的范围上，在这种情况下，仅有的一个等值判断条件LENGTH(BEGIN)是非常低效的，这时一条记录根据这个等值条件会关联到近万条记录，设置关联到两万多条记录，显然大量的实践消耗在低效的连接过程中。

再来看一下具体的SQL语句，会发现几乎没有办法建立索引，因为LENGTH(BEGIN)的选择度非常查，而其他的条件都是不等查询，选择度也不会好，即使建立索引，强制执行选择索引，效率也不会好。

那么如果想要继续优化这个SQL，就只剩下一个办法，那就是SQL的改写。对于自关联查询而言，最佳的改写方法是利用分析函数，其强大的行级处理能力，可以在一次扫描过程中获得一条记录与其他记录的关系，从而消除了自关联的必要性。

SQL改写结果如下：

SELECTBEGIN,OLDENDEND,LENGTH(BEGIN)FROM(SELECTBEGIN,OLDEND,END,LENGTH(BEGIN),COUNT(*)OVER(PARTITIONBYLENGTH(BEGIN),BEGIN,OLDEND)CN,ROW_NUMBER()OVER(PARTITIONBYLENGTH(BEGIN),ENDORDERBYBEGIN)RNFROM(SELECTBEGIN,ENDOLDEND,MAX(END)OVER(PARTITIONBYLENGTH(BEGIN)ORDERBYBEGIN,ENDDESC)ENDFROMT_NUM))WHERERN=1ANDCN=1;

简单的说，内层的分析函数MAX用来根据BEGIN从小到大，END从大到小的条件，确定每个范围对应的最大的END的值。而外层的两个分析函数，COUNT用来去掉完全重复的记录，而ROW_NUMBER用来获取范围最大的记录（也就是没有被其他记录的范围所涵盖）。

改写后，这个SQL避免对自关联，也就不存在关联条件重复值过高的性能隐患了。在模拟环境中，性能对比如下：

SQL>SELECTBEGIN,END,ROWID,LENGTH(BEGIN)2FROMT_NUMA3WHERENOTEXISTS(4SELECT15FROMT_NUMB6WHEREB.BEGIN<=A.BEGIN7ANDB.END>=A.END8ANDB.ROWID!=A.ROWID9ANDLENGTH(B.BEGIN)=LENGTH(A.BEGIN))10;48344rowsselected.Elapsed:00:00:57.68ExecutionPlan———————————————————-Planhashvalue:2540751655————————————————————————————|Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time|————————————————————————————|0|SELECTSTATEMENT||48454|1703K||275(1)|00:00:04||*1|HASHJOINANTI||48454|1703K|1424K|275(1)|00:00:04||2|TABLEACCESSFULL|T_NUM|48454|851K||68(0)|00:00:01||3|TABLEACCESSFULL|T_NUM|48454|851K||68(0)|00:00:01|————————————————————————————PredicateInformation(identifiedbyoperationid):—————————————————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———————————————————-0recursivecalls0dbblockgets404consistentgets0physicalreads0redosize2315794bytessentviaSQL*Nettoclient35966bytesreceivedviaSQL*Netfromclient3224SQL*Netroundtripsto/fromclient0sorts(memory)0sorts(disk)48344rowsprocessedSQL>SELECTBEGIN,OLDENDEND,LENGTH(BEGIN)2FROM(3SELECTBEGIN,OLDEND,END,LENGTH(BEGIN),COUNT(*)OVER(PARTITIONBYLENGTH(BEGIN),BEGIN,OLDEND)CN,4ROW_NUMBER()OVER(PARTITIONBYLENGTH(BEGIN),ENDORDERBYBEGIN)RN5FROM6(7SELECTBEGIN,ENDOLDEND,MAX(END)OVER(PARTITIONBYLENGTH(BEGIN)ORDERBYBEGIN,ENDDESC)END8FROMT_NUM9)10)11WHERERN=112ANDCN=1;48344rowsselected.Elapsed:00:00:00.72ExecutionPlan———————————————————-Planhashvalue:1546715670——————————————————————————————|Id|Operation|Name|Rows|Bytes|TempSpc|Cost(%CPU)|Time|——————————————————————————————|0|SELECTSTATEMENT||48454|2460K||800(1)|00:00:10||*1|VIEW||48454|2460K||800(1)|00:00:10||*2|WINDOWSORTPUSHEDRANK||48454|1845K|2480K|800(1)|00:00:10||3|WINDOWBUFFER||48454|1845K||800(1)|00:00:10||4|VIEW||48454|1845K||311(1)|00:00:04||5|WINDOWSORT||48454|662K|1152K|311(1)|00:00:04||6|TABLEACCESSFULL|T_NUM|48454|662K||68(0)|00:00:01|——————————————————————————————PredicateInformation(identifiedbyoperationid):—————————————————1–filter(“RN”=1AND“CN”=1)2–filter(ROW_NUMBER()OVER(PARTITIONBYLENGTH(TO_CHAR(“BEGIN”)),”END”ORDERBY“BEGIN”)<=1)Statistics———————————————————-0recursivecalls0dbblockgets202consistentgets0physicalreads0redosize1493879bytessentviaSQL*Nettoclient35966bytesreceivedviaSQL*Netfromclient3224SQL*Netroundtripsto/fromclient3sorts(memory)0sorts(disk)48344rowsprocessed

原SQL运行时间接近1分钟，而改写后的SQL语句只需要0.72秒，执行时间变为原本的1/80，逻辑读减少一半。

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