一、     查询要求

Q2 语句查询在给定的区域内，对于某一类型和大小的零件，哪个供应者能以最低的价格供应它。如果那一区域的供应者以同样最低的价格供应所要求的零件，查询列出帐户余额在前 100 位的供应者。对于每一个供应者，查询列出供应者的帐户余额、名字、国家、零件的号码、生产者、供应者的地址、电话号码和备注信息。

Q2语句的特点是：带有排序、聚集操作、子查询并存的多表查询操作。查询语句没有从语法上限制返回多少条元组，TPC-H标准规定，查询结果只返回前100行即可（通常依赖于应用程序实现）。

 

二、     Oracle执行

Oracle编写的查询SQL语句如下：

select * from (

         select   /*+ parallel(n) */

                   s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment

         from part,supplier,partsupp,nation,region

         where

                   p_partkey = ps_partkey

                   and s_suppkey = ps_suppkey

                   and p_size = 25

                   and p_type like '%COPPER'

                   and s_nationkey = n_nationkey

                   and n_regionkey = r_regionkey

                   and r_name = 'ASIA'

                   and ps_supplycost = (

                            select

                                     min(ps_supplycost)

                            from

                                     partsupp,

                                     supplier,

                                     nation,

                                     region

                            where

                                     p_partkey = ps_partkey

                                     and s_suppkey = ps_suppkey

                                     and s_nationkey = n_nationkey

                                     and n_regionkey = r_regionkey

                                     and r_name = 'ASIA'

                   )

         order by

                   s_acctbal desc,n_name,s_name,p_partkey

)

where rownum <= 100;

其中/*+ parallel(n) */ 是Oracle的并行查询语法，n是并行数。

脚本执行时间，单位：秒

并行数	1	2	4	8	12
Oracle	56	35	23	16	27

 

三、     SPL优化

仔细分析这句SQL，如果把子查询

                            select

                                     *

                            from

                                     part,

                                     partsupp,

                                     supplier,

                                     nation,

                                     region

                            where

                                     p_partkey = ps_partkey

                                     and s_suppkey = ps_suppkey

                                     and s_nationkey = n_nationkey

                                     and n_regionkey = r_regionkey

                                     and r_name = 'ASIA'

                                     and p_size = 25

                                      and p_type like '%COPPER'

看成是某个视图V，原来查询主体可以改写成：

         select   /*+ parallel(n) */

                   s_acctbal,s_name,n_name,p_partkey,p_mfgr,s_address,s_phone,s_comment

         from V

         where

                   ps_supplycost = (

                            select

                                     min(ps_supplycost)

                            from

                                     V V1

                            where

                                     V.p_partkey = V1.p_partkey

                   )

这样将原查询变成一个单表查询，相当于找出V中这样一些记录，使得这些记录的ps_supplycost值在所有与该记录的partkey值相同的记录中取值最小。这个运算的本质是对V按partkey分组后对每组聚合，计算出每组中ps_supplycost最小的那条记录。但是，SQL不支持这种聚合运算，于是只能写成子查询的情况（即使转换成单表运算后）。

如果数据库优化引擎不好，严格按这个子查询描述的方法去遍历计算，就会导致N*N的复杂度（N是V的记录数）；即使数据库优化引擎较好，也需要先对V按partkey分组求ps_supplycost的最小值后做形成中间结果集再做索引，然后再次遍历V，计算量也不少。

 

解决这个问题更好的办法就是支持这种返回记录本身的聚合计算，一次分组聚合即可完成运算。SPL有集合和引用数据类型，也支持聚合出最小值所在记录的聚合运算，可以实现这个想法，整体复杂度就会低很多。

SPL脚本如下：

	A
1	=now()
2	>size=25
3	>type="COPPER"
4	>name="ASIA"
5	=file("region.btx").import@b().select(R_NAME==name).derive@o().keys@i(R_REGIONKEY)
6	=file("nation.btx").import@b().switch@i(N_REGIONKEY,A5).keys@i(N_NATIONKEY)
7	=file("part.ctx").open().cursor@m(P_PARTKEY,P_MFGR;P_SIZE==size   && pos@t(P_TYPE,type)).fetch().keys@im(P_PARTKEY)
8	=file("supplier.ctx").open().cursor@m(S_SUPPKEY,S_NAME,S_ADDRESS,S_NATIONKEY,S_PHONE,S_ACCTBAL,S_COMMENT;S_NATIONKEY:A6).fetch().keys@im(S_SUPPKEY)
9	=file("partsupp.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;PS_PARTKEY:A7,PS_SUPPKEY:A8)
10	=A9.groups(PS_PARTKEY;top@1(1;PS_SUPPLYCOST):rs).(rs)
11	=A10.new(PS_SUPPKEY.S_ACCTBAL,PS_SUPPKEY.S_NAME,PS_SUPPKEY.S_NATIONKEY.N_NAME,PS_PARTKEY.P_PARTKEY,PS_PARTKEY.P_MFGR,PS_SUPPKEY.S_ADDRESS,PS_SUPPKEY.S_PHONE,PS_SUPPKEY.S_COMMENT)
12	=A11.sort(S_ACCTBAL:-1,N_NAME,S_NAME,P_PARTKEY).to(100)
13	return interval@ms(A1,now())

需要解释的是，SPL相对于SQL更底层一些，SPL不象SQL有元数据概念，也没有系统级的表概念，数据访问直接从文件开始读取数据，这时前面准备数据的代码就显得稍微冗长一点。实际应用中可以通过使用SPL预定义全程变量或虚表语法来简化，达到类似SQL直接使用数据表的效果。但这不是本篇的重点，而且为了让读者更方便地看出数据的原始流向，这里就采用了直接文件访问的语法。

 

代码中A5-A9用于定义上述视图V的游标，A10中用groups内的top函数实现分组的同时聚合出最小值所在记录（而不是最小值本身）。

A6中的switch@i函数将把外键不能匹配的记录过滤掉，同时将能匹配的关联字段转换成外键表记录的指针，这样在后面可以直接用.的形式访问外键表的字段。SPL看待JOIN运算的思路和SQL不一样，如果数据能事先加载进内存，SPL以利用预关联提高运算性能。不过，本系列例子均假定从外存取数计算，本问题中SPL和SQL在JOIN运算性能也没有算法上的区别，只是写法不同。详细解释可参考SPL教案中关于JOIN的部分。

另外，在A7中也使用了Q1中提到的在游标建立时使用过滤条件的技巧。A8和A9中将这个技巧与前面的switch@i方法结合起来（第2组参数），在游标建立时做外键匹配，不能匹配者直接过滤掉，不再读取其它字段且不再生成该记录，能匹配时则将关联字段转换成指针。

 

脚本执行时间，单位：秒

并行数	1	2	4	8	12
Oracle	56	35	23	16	27
SPL组表	20	14	8	5	4

这个问题的数据量不大，几次执行后，操作系统可以把数据都缓存进内存，列存在这里不是重点，带来的访问量优势可以忽略，性能优势主要是算法优化带来的。

从表中还能看出，这种分组聚合的并行效果也较好。