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

最新推荐文章于 2022-04-20 13:56:26 发布

ertyee42

最新推荐文章于 2022-04-20 13:56:26 发布

阅读量175

点赞数

原文链接：http://c.raqsoft.com.cn/article/1569547077045

版权

一、查询要求

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)