从 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

 

展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客
应支付0元
点击重新获取
扫码支付

支付成功即可阅读