"一、查询要求 Q8语句是查询在过去的两年中一个给定零件类型在某国某地区市场份额的变化情况。 Q8语句的特点是：带有分组、排序、聚集、子查询操 .."

sjr 北京
乾学院 32 号会员
1 回帖 • 1,115 浏览 • 5 年前

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

计算＆AI

集算器(484)

一、查询要求

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	=now()
2	1995-01-01
3	1996-12-31
4	>nation="CHINA"
5	>name="ASIA"
6	>type="STANDARD POLISHED BRASS"
7	=file("region.btx").import@b().select(R_NAME==name).derive@o().keys@i(R_REGIONKEY)
8	=file("nation.btx").import@b().select(N_NAME==nation).switch@i(N_REGIONKEY,A7).derive@o().keys@i(N_NATIONKEY)
9	=file("nation.btx").import@b().switch@i(N_REGIONKEY,A7).derive@o().keys@i(N_NATIONKEY)
10	=file("supplier.ctx").open().cursor@m(S_SUPPKEY;A8.find(S_NATIONKEY)).fetch().keys@im(S_SUPPKEY)
11	=file("part.ctx").open().cursor@m(P_PARTKEY;P_TYPE==type).fetch().keys@im(P_PARTKEY)
12	=file("customer.ctx").open().cursor@m(C_CUSTKEY;A9.find(C_NATIONKEY)).fetch().keys@im(C_CUSTKEY)
13	=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_ORDERDATE;O_ORDERDATE>=A2 && O_ORDERDATE <=A3 && A12.find(O_CUSTKEY))
14	=file("lineitem.ctx").open().news(A13,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE;A11.find(L_PARTKEY))
15	=A14.switch(L_SUPPKEY,A10)
16	=A15.run(L_EXTENDEDPRICE*=(1-L_DISCOUNT))
17	=A16.groups( year(O_ORDERDATE):o_year;sum(if(L_SUPPKEY,L_EXTENDEDPRICE,0)):s1,sum(L_EXTENDEDPRICE):s2)
18	=A17.new(o_year,s1/s2:mkt_share)
19	return interval@ms(A1,now())

nation表作为外键表被用了两次，这里在A8,A9分别生成所需的外键表。