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

润乾软件

于 2020-04-09 16:00:06 发布

阅读量152

点赞数

CC 4.0 BY-SA版权

分类专栏：集算器

本文链接：https://blog.youkuaiyun.com/raqsoft/article/details/105412776

集算器专栏收录该内容

263 篇文章

订阅专栏

一、查询要求

Q7语句是查询从供货商国家与销售商品的国家之间通过销售获利情况的查询。此查询确定在两国之间货运商品的量用以帮助重新谈判货运合同。

Q7语句的特点是：带有分组、排序、聚集、子查询操作并存的多表查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询。

二、 Oracle执行

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

select /*+ parallel(n) */

supp_nation,

cust_nation,

l_year,

sum(volume) as revenue

from

(

select

n1.n_name as supp_nation,

n2.n_name as cust_nation,

extract(year from l_shipdate) as l_year,

l_extendedprice * (1 - l_discount) as volume

from

supplier,

lineitem,

orders,

customer,

nation n1,

nation n2

where

s_suppkey = l_suppkey

and o_orderkey = l_orderkey

and c_custkey = o_custkey

and s_nationkey = n1.n_nationkey

and c_nationkey = n2.n_nationkey

and (

(n1.n_name = 'CHINA' and n2.n_name = 'RUSSIA')

or (n1.n_name = 'RUSSIA' and n2.n_name = 'CHINA')

)

and l_shipdate between date '1995-01-01' and date '1996-12-31'

) shipping

group by

supp_nation,

cust_nation,

l_year

order by

supp_nation,

cust_nation,

l_year;

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

脚本执行时间，单位：秒

并行数	1	2	4	8	12
Oracle	510	344	256	211	184

三、 SPL优化

中间子查询的运算和Q3类似，优化原理也类似，这里就不再赘述。

SPL脚本如下：

	A
1	=1
2	=now()
3	>name1="CHINA"
4	>name2="RUSSIA"
5	=file(path+"nation.ctx").create().cursor(N_NATIONKEY,N_NAME;N_NAME==name1 \|\| N_NAME == name2).fetch().keys@i(N_NATIONKEY)
6	=file(path+"supplier.ctx").create().cursor@m(S_SUPPKEY,S_NATIONKEY;S_NATIONKEY:A5;A1).fetch().keys@i(S_SUPPKEY)
7	=file(path+"customer.ctx").create().cursor@m(C_CUSTKEY,C_NATIONKEY;C_NATIONKEY:A5;A1).fetch().keys@i(C_CUSTKEY)
8	=file(path+"orders.ctx").create().cursor@m(O_ORDERKEY,O_CUSTKEY;O_CUSTKEY:A7;A1)
9	1995-01-01
10	1996-12-31
11	=file(path+"lineitem.ctx").create().news(A8,L_ORDERKEY,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,L_SHIPDATE,O_CUSTKEY;L_SHIPDATE>=A9 && L_SHIPDATE <=A10,L_SUPPKEY:A6)
12	=A11.select(O_CUSTKEY.C_NATIONKEY.N_NAME!=L_SUPPKEY.S_NATIONKEY.N_NAME)
13	=A12.groups( L_SUPPKEY.S_NATIONKEY.N_NAME:supp_nation,O_CUSTKEY.C_NATIONKEY.N_NAME:cust_nation,year(L_SHIPDATE):l_year; sum(L_EXTENDEDPRICE * (1 - L_DISCOUNT)): volume)
14	=now()
15	=interval@s(A2,A14)