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

一、     查询要求

 

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)

nation表作为外键表被用了两次，这里在A8,A9基于读出来的A7分别生成相应的外键表。

 

脚本执行时间，单位：秒

并行数	1	2	4	8	12
Oracle	472	362	277	216	192
SPL组表	315	162	92	46	37