从 TPCH 测试学习性能优化技巧之 Q16
一、 查询要求
Q16语句查询能够以指定的属性供应零件的供货商数量。可用于决定在订单量大，任务紧急时，是否有充足的供货商。
Q16语句的特点是：带有分组、排序、聚集、去重、NOT IN子查询操作并存的两表连接操作。
二、 Oracle执行
Oracle编写的查询SQL语句如下：
select /*+ parallel(n) */
p_brand,p_type,p_size,
count(distinct ps_suppkey) as supplier_cnt
from
partsupp,part
where
p_partkey = ps_partkey
and p_brand <> 'Brand#21'
and p_type not like 'SMALL%'
and p_size in (2, 15, 17, 23, 25, 41, 44, 45)
and ps_suppkey not in (
select
s_suppkey
from
supplier
where
s_comment like '%Customer%Complaints%'
)
group by
p_brand,
p_type,
p_size
order by
supplier_cnt desc,
p_brand,
p_type,
p_size;
其中/*+ parallel(n) */ 是Oracle的并行查询语法，n是并行数。
脚本执行时间，单位：秒
并行数 |
1 |
2 |
4 |
8 |
12 |
Oracle |
57 |
34 |
23 |
15 |
13 |
三、 SPL优化
这个查询是将partsupp用两个外键表part和supplier进行匹配过滤后再分组，分组时将用两个外键表字段。这时候采用前面所述的技巧，先将外键表按条件过滤后，再将主表的关联字段与外键表匹配并把匹配上的关联字段转换成外键表的记录指针，这样匹配不上的记录 可直接过滤掉，而且在后面分组运算时即可直接引用外键表字段。
SPL脚本如下：
A |
|
1 |
=now() |
2 |
>brand="Brand#21" |
3 |
>type="SMALL" |
4 |
>sizes=[2,15,17,23,25,41,44,45] |
5 |
=file("part.ctx").open().cursor@m(P_PARTKEY,P_BRAND,P_TYPE,P_SIZE;P_BRAND!=brand && !pos@h(P_TYPE,type) && sizes.contain@b(P_SIZE)).fetch().keys@im(P_PARTKEY) |
6 |
=file("supplier.ctx").open().cursor@m(S_SUPPKEY;!like(S_COMMENT,"*Customer*Complaints*")).fetch().keys@im(S_SUPPKEY) |
7 |
=file("partsupp.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY;PS_PARTKEY:A5,PS_SUPPKEY:A6) |
8 |
=A7.groups@u(PS_PARTKEY.P_BRAND,PS_PARTKEY.P_TYPE,PS_PARTKEY.P_SIZE;icount(PS_SUPPKEY):supplier_cnt) |
9 |
=A8.sort(-supplier_cnt,P_BRAND,P_TYPE,P_SIZE) |
10 |
return interval@ms(A1,now()) |
A5和A6分别读来并过滤外键表，A7建立游标同时做匹配和过滤。
注意A5中做in判断时使用了contain@b，表示这里将采用二分法。当in判断的集合成员较多时，将成员先排序后使用二分法可以减少比较次数，从而提高性能。
脚本执行时间，单位：秒
并行数 |
1 |
2 |
4 |
8 |
12 |
Oracle |
57 |
34 |
23 |
15 |
13 |
SPL组表 |
39 |
26 |
18 |
11 |
10 |
本问题涉及数据量不大，SPL的运算结果与SQL差别也不大。
