一、 查询要求
Q7语句是查询从供货商国家与销售商品的国家之间通过销售获利情况的查询。此查询确定在两国之间货运商品的量用以帮助重新谈判货运合同。
Q7语句的特点是：带有分组、排序、聚集、子查询操作并存的多表查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询。
二、 Oracle执行
Oracle编写的查询SQL语句如下：
select /*+ parallel(n) */
supp_nation,
cust_nation,
l_year,
sum(volume) as revenue
from
(
select
n1.n_name as supp_nation,
n2.n_name as cust_nation,
extract(year from l_shipdate) as l_year,
l_extendedprice * (1 - l_discount) as volume
from
supplier,
lineitem,
orders,
customer,
nation n1,
nation n2
where
s_suppkey = l_suppkey
and o_orderkey = l_orderkey
and c_custkey = o_custkey
and s_nationkey = n1.n_nationkey
and c_nationkey = n2.n_nationkey
and (
(n1.n_name = 'CHINA' and n2.n_name = 'RUSSIA')
or (n1.n_name = 'RUSSIA' and n2.n_name = 'CHINA')
)
and l_shipdate between date '1995-01-01' and date '1996-12-31'
) shipping
group by
supp_nation,
cust_nation,
l_year
order by
supp_nation,
cust_nation,
l_year;
其中/*+ parallel(n) */ 是Oracle的并行查询语法，n是并行数。
脚本执行时间，单位：秒
| 并行数 | 1 | 2 | 4 | 8 | 12 |
| Oracle | 510 | 344 | 256 | 211 | 184 |
三、 SPL优化
中间子查询的运算和Q3类似，优化原理也类似，这里就不再赘述。
SPL脚本如下：
| A | |
| 1 | =1 |
| 2 | =now() |
| 3 | >name1="CHINA" |
| 4 | >name2="RUSSIA" |
| 5 | =file(path+"nation.ctx").create().cursor(N_NATIONKEY,N_NAME;N_NAME==name1 || N_NAME == name2).fetch().keys@i(N_NATIONKEY) |
| 6 | =file(path+"supplier.ctx").create().cursor@m(S_SUPPKEY,S_NATIONKEY;S_NATIONKEY:A5;A1).fetch().keys@i(S_SUPPKEY) |
| 7 | =file(path+"customer.ctx").create().cursor@m(C_CUSTKEY,C_NATIONKEY;C_NATIONKEY:A5;A1).fetch().keys@i(C_CUSTKEY) |
| 8 | =file(path+"orders.ctx").create().cursor@m(O_ORDERKEY,O_CUSTKEY;O_CUSTKEY:A7;A1) |
| 9 | 1995-01-01 |
| 10 | 1996-12-31 |
| 11 | =file(path+"lineitem.ctx").create().news(A8,L_ORDERKEY,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,L_SHIPDATE,O_CUSTKEY;L_SHIPDATE>=A9 && L_SHIPDATE <=A10,L_SUPPKEY:A6) |
| 12 | =A11.select(O_CUSTKEY.C_NATIONKEY.N_NAME!=L_SUPPKEY.S_NATIONKEY.N_NAME) |
| 13 | =A12.groups( L_SUPPKEY.S_NATIONKEY.N_NAME:supp_nation,O_CUSTKEY.C_NATIONKEY.N_NAME:cust_nation,year(L_SHIPDATE):l_year; sum(L_EXTENDEDPRICE * (1 - L_DISCOUNT)): volume) |
| 14 | =now() |
| 15 | =interval@s(A2,A14) |
注意nation表数据A5在A6和A7分别使用了一次，用于外键匹配过滤，这和SQL的别名写法不同。
脚本执行时间，单位：秒
| 并行数 | 1 | 2 | 4 | 8 | 12 |
| Oracle | 510 | 344 | 256 | 211 | 184 |
| SPL组表 | 250 | 126 | 66 | 34 | 25 |