"一、查询要求 Q5 语句查询出在某一年某一地区各个国家的收入，这些收入是通过那些定购零件的顾客和供应零件的供应者都在那个国家的交易获得的。这 .."

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

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

计算＆AI

集算器(489)

一、查询要求

Q5 语句查询出在某一年某一地区各个国家的收入，这些收入是通过那些定购零件的顾客和供应零件的供应者都在那个国家的交易获得的。这个查询按照收入的降序显示出国家和收入大小。

Q5语句的特点是：带有分组、排序、聚集操作并存的多表连接查询操作。

二、 Oracle执行

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

select /*+ parallel(n) */

n_name,

sum(l_extendedprice * (1 - l_discount)) as revenue

from

customer,

orders,

lineitem,

supplier,

nation,

region

where

c_custkey = o_custkey

and l_orderkey = o_orderkey

and l_suppkey = s_suppkey

and c_nationkey = s_nationkey

and s_nationkey = n_nationkey

and n_regionkey = r_regionkey

and r_name = 'ASIA'

and o_orderdate >= date '1995-01-01'

and o_orderdate < date '1995-01-01' + interval '1' year

group by

n_name

order by

revenue desc;

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

脚本执行时间，单位：秒

并行数	1	2	4	8	12
Oracle	672	368	301	224	225

三、 SPL优化

优化原理和Q3类似，只是涉及的外键表更多一些。

SPL脚本如下：

	A
1	=now()
2	1995-1-1
3	=elapse@y(A2,1)
4	>name="ASIA"
5	=file("region.btx").import@b().select(R_NAME==name).derive@o().keys@i(R_REGIONKEY)
6	=file("nation.btx").import@b().switch@i(N_REGIONKEY,A5).derive@o().keys@i(N_NATIONKEY)
7	=file("supplier.ctx").open().cursor@m(S_SUPPKEY,S_NATIONKEY;S_NATIONKEY:A6).fetch().keys@im(S_SUPPKEY)
8	=file("customer.ctx").open().cursor@m(C_CUSTKEY,C_NATIONKEY;C_NATIONKEY:A6).fetch().keys@im(C_CUSTKEY)
9	=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_CUSTKEY;O_ORDERDATE>=A2 && O_ORDERDATE < A3,O_CUSTKEY:A8)
10	=file("lineitem.ctx").open().news(A9,L_ORDERKEY,L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT,O_CUSTKEY;L_SUPPKEY:A7)
11	=A10.select(O_CUSTKEY.C_NATIONKEY==L_SUPPKEY.S_NATIONKEY)
12	=A11.groups@u(L_SUPPKEY.S_NATIONKEY.N_NAME;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)):revenue)
13	=A12.sort@z(revenue)
14	return interval@ms(A1,now())

这里大量使用了前面题目中说过的游标建立时过滤和将关联字段转换成外键表指针的技巧。

与Q3不大相同的是，最后用于分组的字段不是已经有序的L_ORDERKEY，所以不能再使用groups@o。