从 TPCH 测试学习性能优化技巧之 Q17
一、 查询要求
Q17 语句对于指定品牌和指定包装类型的零件，查询出订购量少于此类零件平均订购量 20％的订单平均每年的订购额。所以此查询可用于计算出如果没有小量订单，平均年收入将损失多少。
Q17语句的特点是：带有聚集、聚集子查询操作并存的两表连接操作。
二、 Oracle执行
Oracle编写的查询SQL语句如下：
select /*+ parallel(n) */
sum(l_extendedprice) / 7.0 as avg_yearly
from
lineitem,part
where
p_partkey = l_partkey
and p_brand = 'Brand#33'
and p_container = 'LG DRUM'
and l_quantity < (
select
0.2 * avg(l_quantity)
from
lineitem
where
l_partkey = p_partkey
);
其中/*+ parallel(n) */ 是Oracle的并行查询语法，n是并行数。
脚本执行时间，单位：秒
并行数 |
1 |
2 |
4 |
8 |
12 |
Oracle |
363 |
278 |
230 |
173 |
165 |
三、 SPL优化
这种在子查询中用等值条件与主表关联的情况，都可以转换成JOIN来计算，从而可以利用JOIN的优化技术。
select /*+ parallel(n) */
sum(l_extendedprice) / 7.0 as avg_yearly
from
lineitem,part,
(select l_partkey lp, 0.2*avg(l_quantity) lq
from lineitem
group by l_partkey
) lpq
where
p_partkey = l_partkey
and p_brand = 'Brand#33'
and p_container = 'LG DRUM'
and l_partkey=lp
and l_quantity < lq
这相当于对lineitem表进行两次外键匹配过滤，其中一个外键表是子查询计算出来的中间表lpq，而因为是内连接，lpq中涉及到的partkey也只在part表过滤后的范围内，因此可以复用part表的过滤。
SPL脚本如下：
A |
|
1 |
=now() |
2 |
>brand="Brand#33" |
3 |
>container="LG DRUM" |
4 |
=file("part.ctx").open().cursor@m(P_PARTKEY;P_BRAND==brand && P_CONTAINER==container).fetch().keys@im(P_PARTKEY) |
5 |
=file("lineitem.ctx").open() |
6 |
=A5.cursor@m(L_PARTKEY,L_QUANTITY;A4.find(L_PARTKEY)) |
7 |
=A6.groups@u(L_PARTKEY;avg(L_QUANTITY):avg).run(avg/=5).keys@im(L_PARTKEY) |
8 |
=A5.cursor@m(L_PARTKEY,L_QUANTITY,L_EXTENDEDPRICE;L_PARTKEY:A7) |
9 |
=A8.total(sum(if(L_QUANTITY<L_PARTKEY.avg,L_EXTENDEDPRICE,0)))/7.0 |
10 |
return interval@ms(A1,now()) |
脚本执行时间，单位：秒
并行数 |
1 |
2 |
4 |
8 |
12 |
Oracle |
363 |
278 |
230 |
173 |
165 |
SPL组表 |
94 |
49 |
26 |
18 |
17 |
