一、 查询要求
Q21语句查询获得不能及时交货的供货商。
Q21语句的特点是:带有分组、排序、聚集、EXISTS子查询、NOT EXISTS子查询操作并存的四表连接操作。查询语句没有从语法上限制返回多少条元组,但是TPC-H标准规定,查询结果只返回前100行(通常依赖于应用程序实现)。
二、 Oracle执行
Oracle编写的查询SQL语句如下:
select * from (
select /*+ parallel(n) */
s_name,
count(*) as numwait
from
supplier,lineitem l1,orders,nation
where
s_suppkey = l1.l_suppkey
and o_orderkey = l1.l_orderkey
and o_orderstatus = 'F'
and l1.l_receiptdate > l1.l_commitdate
and exists (
select
*
from
lineitem l2
where
l2.l_orderkey = l1.l_orderkey
and l2.l_suppkey <> l1.l_suppkey
)
and not exists (
select
*
from
lineitem l3
where
l3.l_orderkey = l1.l_orderkey
and l3.l_suppkey <> l1.l_suppkey
and l3.l_receiptdate > l3.l_commitdate
)
and s_nationkey = n_nationkey
and n_name = 'CHINA'
group by
s_name
order by
numwait desc,
s_name
) where rownum<=100;
其中/*+ parallel(n) */ 是Oracle的并行查询语法,n是并行数。
脚本执行时间,单位:秒
并行数 | 1 | 2 | 4 | 8 | 12 |
Oracle | 978 | 634 | 463 | 363 | 326 |
三、 SPL优化
本问题的主查询比较简单,就是前面介绍过的主子表和外键表的连接。这里麻烦的主要是两个有exists的子查询。仔细研究这两个子查询可以发现,它们都是针对同一个l_orderkey下的lineitem记录的一些运算。
我们知道,lineitem已经按l_orderkey排序,而且子表记录可以看作是主表的集合字段,如果我们将orders和lineitem的连接结果集按orderkey做有序分组(但先不聚合),可以得到一个个由相同l_orderkey值的lineitem记录的小集合,再在这个小集合中去计算上述那两个exists条件就会比较简单一些。
SQL没有显式的集合数据类型,没办法描述这种运算过程。而SPL提供有这种数据类型,就可以实现这个思路了。
SPL脚本如下:
A | |
1 | =1 |
2 | =now() |
3 | >name="CHINA" |
4 | =file(path+"orders.ctx").create().cursor@m(O_ORDERKEY;O_ORDERSTATUS == "F";A1) |
5 | =file(path+"lineitem.ctx").create().news(A4,L_ORDERKEY,L_SUPPKEY,L_COMMITDATE,L_RECEIPTDATE) |
6 | =A5.group(L_ORDERKEY) |
7 | =A6.conj( if( (t=~.select(~.L_RECEIPTDATE > ~.L_COMMITDATE)) && (s=t.m(1).L_SUPPKEY)&& !t.select@1(~.L_SUPPKEY!=s) && ~.select@1(~.L_SUPPKEY!=s), t, null ) ) |
8 | =file(path+"nation.ctx").create().cursor(N_NATIONKEY;N_NAME == name).fetch().keys@i(N_NATIONKEY) |
9 | =file(path+"supplier.ctx").create().cursor@m(S_SUPPKEY,S_NAME;A8.find(S_NATIONKEY);A1) |
10 | =A9.fetch() |
11 | =A7.switch@i(L_SUPPKEY,A10:S_SUPPKEY) |
12 | =A11.groups@u(L_SUPPKEY.S_NAME:s_name;count(1):numwait) |
13 | =A12.top(100;[-numwait,s_name]) |
14 | =now() |
15 | =interval@s(A2,A14) |
A5中对orders和lineitem做连接,A6再对其做有序分组,分成由相同l_orderkey值的lineitem记录集合,A7中对这些小集合实现exists判断,过滤掉不满足条件的。后面的代码把其它外键表关联上再做常规分组运算即可。
脚本执行时间,单位:秒
并行数 | 1 | 2 | 4 | 8 | 12 |
Oracle | 978 | 634 | 463 | 363 | 326 |
SPL组表 | 259 | 163 | 83 | 40 | 27 |