从 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排序,而且子表记录可以看作是主表的集合字段,如果我们将orderslineitem的连接结果集按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中对orderslineitem做连接,A6再对其做有序分组,分成由相同l_orderkey值的lineitem记录集合,A7中对这些小集合实现exists判断,过滤掉不满足条件的。后面的代码把其它外键表关联上再做常规分组运算即可。

 

脚本执行时间,单位:秒

并行数

1

2

4

8

12

Oracle

978

634

463

363

326

SPL组表

259

163

83

40

27