一、 查询要求
Q11语句是查询库存中某个国家供应的零件的价值。
Q11语句的特点是：带有分组、排序、聚集、子查询操作并存的多表连接查询操作。子查询位于分组操作的HAVING条件中。
二、 Oracle执行
Oracle编写的查询SQL语句如下：
select /*+ parallel(n) */
ps_partkey,
sum(ps_supplycost * ps_availqty) as value
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'CHINA'
group by
ps_partkey
having
sum(ps_supplycost * ps_availqty) > (
select
sum(ps_supplycost * ps_availqty) * 0.000001
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'CHINA'
)
order by
value desc;
其中/*+ parallel(n) */ 是Oracle的并行查询语法，n是并行数。
脚本执行时间，单位：秒
| 并行数 | 1 | 2 | 4 | 8 | 12 |
| Oracle | 71 | 52 | 43 | 36 | 33 |
三、 SPL优化
我们把下面的子查询看成为视图V：
select
ps_partkey,
sum(ps_supplycost * ps_availqty) as value
from
partsupp,
supplier,
nation
where
ps_suppkey = s_suppkey
and s_nationkey = n_nationkey
and n_name = 'CHINA'
则原主体查询等价于
select
ps_partkey,
value
from V
where value>0.000001*(select sum(value) from V)
这个V已经是经过分组后的结果集，数量较小，遍历V比直接针对partsupp遍历的计算量要小很多。
而我们还知道，partsupp表是按主键ps_partkey,ps_suppkey有序的，也就是对ps_partkey有序，这时候针对该字段的分组可以使用有序分组方案，以提高计算V的性能。
SPL脚本如下：
|
|
A |
| 1 | =1 |
| 2 | =now() |
| 3 | >name="CHINA" |
| 4 | >percent=0.000001 |
| 5 | =file(path+"nation.ctx").create().cursor(N_NATIONKEY,N_NAME;N_NAME == name).fetch().keys@i(N_NATIONKEY) |
| 6 | =file(path+"supplier.ctx").create().cursor@m(S_SUPPKEY;A5.find(S_NATIONKEY);A1) |
| 7 | =A6.fetch().keys@i(S_SUPPKEY) |
| 8 | =file(path+"partsupp.ctx").create().cursor@m(PS_PARTKEY,PS_AVAILQTY,PS_SUPPLYCOST;A7.find(PS_SUPPKEY);A1) |
| 9 | =A8.fetch() |
| 10 | =A9.groups@o(PS_PARTKEY;sum(PS_SUPPLYCOST * PS_AVAILQTY):value) |
| 11 | =A10.sum(value) * percent |
| 12 | =A10.select(value > A11) |
| 13 | =A12.sort@o(value:-1) |
| 14 | =now() |
| 15 | =interval@s(A2,A14) |
A10用groups@o执行有序分组，相当于计算出视图V，然后A11,A12对A10进行两次遍历计算出结果。
脚本执行时间，单位：秒
| 并行数 | 1 | 2 | 4 | 8 | 12 |
| Oracle | 71 | 52 | 43 | 36 | 33 |
| SPL组表 | 24 | 15 | 9 | 6 | 5 |