性能优化技巧：遍历复用提速多次分组
乾学院
发表于
1. 单分组单倍计算量
select /*+ parallel(12) */ mod(id,100) Aid,max(amount) Amax from topn group by mod(id,100)2. 单分组双倍计算量
select /*+ parallel(12) */ mod(id,100)+floor(id/20000000) Aid, max(amount) Amax, min(amount) Amin from topn group by mod(id,100)+floor(id/20000000);计算式多了一倍，相当于计算量大了一倍。
3. 双分组双倍计算量
select /*+ parallel(12) */ * from (select mod(id,100) Aid,max(amount) Amax from topn group by mod(id,100) ) Ajoin( select floor(id/20000000) Bid,min(amount) Bmin from topn group by floor(id/20000000) ) Bon A.Aid=B.Bid;
这里的计算量大体与2相同，但有两个分组，我们将观察数据库是否会进行两次遍历。最后的JOIN运算只涉及100行数据，时间可以忽略不计。
1. 单分组单倍计算量
| A | |
| 1 | =now() |
| 2 | =file("/home/topn/topn.ctx").open().cursor@m(id,amount) |
| 3 | =A2.groups@u(id%100:Aid;max(amount):Amax) |
| 4 | =interval@s(A1,now()) |
A2定义多路游标利用并行，并行数由配置文件raqsoftConfig.xml中的cursorParallelNum决定。
2. 单分组双倍计算量
| A | |
| 1 | =now() |
| 2 | =file("/home/topn/topn.ctx").open().cursor@m(id,amount) |
| 3 | =A2.groups@u(id%100+id\20000000:Aid;max(amount):Amax,min(amount):Amin) |
| 4 | =interval@s(A1,now()) |
3. 双分组双倍计算量
| A | B | |
| 1 | =now() | |
| 2 | =file("/home/topn/topn.ctx").open().cursor@m(id,amount) | |
| 3 | cursor A2 | =A3.groups@u(id%100:Aid;max(amount):Amax) |
| 4 | cursor | =A4.groups@u(id\20000000:Bid;max(amount):Bmax) |
| 5 | =A3.join@i(Aid,A4:Bid,Bid,Bmax) | |
| 6 | =interval@s(A1,now()) | |
这里采用了SPL特有的遍历复用语法，在A2定义了游标，A3/B3和A4/B4中定义了两套针对这个游标的计算，表示会在一次游标遍历过程同时计算这两个结果。
| 单分组单倍计算量 | 单分组双倍计算量 | 双分组双倍计算量 | |
| Oracle | 458 | 692 | 878 |
| SPL | 336 | 350 | 376 |
从Oracle的测试结果上看，双分组双倍计算量比单分组双倍计算量慢了近200秒，这不是一个可以忽略的时间了，因为两者的计算量几乎相同，这多出来的时间估计就是多做一次遍历的时间了。这说明数据库不会自动做遍历复用的优化，在双分组时会将数据表遍历两次，结果多做一次分组几乎会多出一倍的时间。
更多性能优化技巧，可在底部“阅读原文”中查看