从 TPCH 测试学习性能优化技巧之 Q21

一、     查询要求

 

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