从 TPCH 测试学习性能优化技巧之 Q9
一、 查询要求
Q9语句是查询每个国家每一年所有被定购的零件在一年中的总利润。
Q9语句的特点是：带有分组、排序、聚集、子查询操作并存的查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询，但子查询自身是多表连接的查询。子查询中使用了LIKE操作符，有的查询优化器不支持对LIKE操作符进行优化。
二、 Oracle执行
Oracle编写的查询SQL语句如下：
select /*+ parallel(n) */
nation,
o_year,
sum(amount) as sum_profit
from
(
select
n_name as nation,
extract(year from o_orderdate) as o_year,
l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity as amount
from
part,
supplier,
lineitem,
partsupp,
orders,
nation
where
s_suppkey = l_suppkey
and ps_suppkey = l_suppkey
and ps_partkey = l_partkey
and p_partkey = l_partkey
and o_orderkey = l_orderkey
and s_nationkey = n_nationkey
and p_name like '%chocolate%'
) profit
group by
nation,
o_year
order by
nation,
o_year desc;
其中/*+ parallel(n) */ 是Oracle的并行查询语法，n是并行数。
脚本执行时间，单位：秒
并行数 |
1 |
2 |
4 |
8 |
12 |
Oracle |
930 |
502 |
331 |
267 |
234 |
三、 SPL优化
这里orders与lineitem主子表关联的优化原理同Q3。
SPL脚本如下：
A |
|
1 |
=now() |
2 |
>name="chocolate" |
3 |
=file("nation.btx").import@b().keys@i(N_NATIONKEY) |
4 |
=file("part.ctx").open().cursor@m(P_PARTKEY, P_NAME;pos(P_NAME, name)).fetch().keys@im(P_PARTKEY) |
5 |
=file("supplier.ctx").open().cursor@m(S_SUPPKEY,S_NATIONKEY;S_NATIONKEY:A3).fetch().keys@im(S_SUPPKEY) |
6 |
=file("partsupp.ctx").open().cursor@m(PS_PARTKEY,PS_SUPPKEY,PS_SUPPLYCOST;A4.find(PS_PARTKEY)).fetch().keys@i(PS_PARTKEY,PS_SUPPKEY) |
7 |
=file("orders.ctx").open().cursor@m(O_ORDERKEY,O_ORDERDATE) |
8 |
=file("lineitem.ctx").open().news(A7,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE) |
9 |
=A8.join@i(L_PARTKEY:L_SUPPKEY,A6,PS_SUPPLYCOST).switch(L_SUPPKEY,A5) |
10 |
=A9.groups(L_SUPPKEY.S_NATIONKEY.N_NAME:nation,year(O_ORDERDATE):o_year;sum(L_EXTENDEDPRICE*(1-L_DISCOUNT)-PS_SUPPLYCOST*L_QUANTITY):profit) |
11 |
=A10.sort(nation,-o_year) |
12 |
return interval@ms(A1,now()) |
A9中用join@i方法把外键表的字段PS_SUPPLYCOST拼到lineitem游标上，这里是个双字段主键的外键表，不能再使用在游标建立时匹配并转换的方法。
脚本执行时间，单位：秒
并行数 |
1 |
2 |
4 |
8 |
12 |
Oracle |
930 |
502 |
331 |
267 |
234 |
SPL组表 |
691 |
354 |
180 |
95 |
68 |
英文版