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

原创于 2020-04-09 16:01:05 发布 · 128 阅读

0 ·

CC 4.0 BY-SA版权

集算器专栏收录该内容

263 篇文章

订阅专栏

本文探讨了TPCH Q8查询，该查询涉及分组、排序、聚集和子查询操作。分析了Oracle执行该查询的SQL语句，并介绍了如何使用SPL进行性能优化，展示了不同执行时间。

一、查询要求

Q8语句是查询在过去的两年中一个给定零件类型在某国某地区市场份额的变化情况。

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

二、 Oracle执行

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

select /*+ parallel(n) */

o_year,

sum(case

when nation = 'CHINA' then volume

else 0

end)/ sum(volume) as mkt_share

from

(

select

extract(year from o_orderdate) as o_year,

l_extendedprice * (1 - l_discount) as volume,

n2.n_name as nation

from

part,

supplier,

lineitem,

orders,

customer,

nation n1,

nation n2,

region

where

p_partkey = l_partkey

and s_suppkey = l_suppkey

and l_orderkey = o_orderkey

and o_custkey = c_custkey

and c_nationkey = n1.n_nationkey

and n1.n_regionkey = r_regionkey

and r_name = 'ASIA'

and s_nationkey = n2.n_nationkey

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

and p_type = 'STANDARD POLISHED BRASS'

) all_nations

group by

o_year

order by

o_year;

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

脚本执行时间，单位：秒

并行数	1	2	4	8	12
Oracle	472	362	277	216	192

三、 SPL优化

这里的orders与lineitem主子表关联优化原理与Q3中类似。

SPL脚本如下：

	A
1	=1
2	=now()
3	>nation="CHINA"
4	>name="ASIA"
5	>type="STANDARD POLISHED BRASS"
6	=file(path+"region.ctx").create().cursor(R_REGIONKEY,R_NAME;R_NAME==name).fetch()
7	=file(path+"nation.ctx").create().cursor(N_NATIONKEY,N_NAME,N_REGIONKEY).fetch()
8	=A7.select(N_NAME==nation).derive@o().keys@i(N_NATIONKEY)
9	=A7.switch@i(N_REGIONKEY, A6:R_REGIONKEY).keys@i(N_NATIONKEY)
10	1995-01-01
11	1996-12-31
12	=file(path+"customer.ctx").create().cursor@m(C_CUSTKEY;A9.find(C_NATIONKEY);A1).fetch().keys@i(C_CUSTKEY)
13	=file(path+"orders.ctx").create().cursor@m(O_ORDERKEY,O_ORDERDATE;O_ORDERDATE>=A10 && O_ORDERDATE <=A11 && A12.find(O_CUSTKEY);A1)
14	=file(path+"supplier.ctx").create().cursor@m(S_SUPPKEY;A8.find(S_NATIONKEY);A1).fetch().keys@i(S_SUPPKEY)
15	=file(path+"part.ctx").create().cursor@m(P_PARTKEY;P_TYPE==type;A1).fetch().keys@i(P_PARTKEY)
16	=file(path+"lineitem.ctx").create().news(A13,L_ORDERKEY,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE;A15.find(L_PARTKEY))
17	=A16.switch(L_SUPPKEY,A14)
18	=A16.new(O_ORDERDATE,L_EXTENDEDPRICE*(1-L_DISCOUNT):volume,if(L_SUPPKEY,volume,0):nvolume)
19	=A18.groups( year(O_ORDERDATE):o_year;sum(nvolume):s1,sum(volume):s2)
20	=A19.new(o_year,s1/s2:mkt_share)
21	=now()
22	=interval@s(A2,A21)