从 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	=1
2	=now()
3	>name="*chocolate*"
4	=file(path+"nation.ctx").create().cursor(N_NATIONKEY,N_NAME).fetch().keys@i(N_NATIONKEY)
5	=file(path+"part.ctx").create().cursor@m(P_PARTKEY,   P_NAME;like(P_NAME, name);A1).fetch().derive@o().keys@i(P_PARTKEY)
6	=file(path+"supplier.ctx").create().cursor@m(S_SUPPKEY,S_NATIONKEY;S_NATIONKEY:A4;A1).fetch().keys@i(S_SUPPKEY)
7	=file(path+"partsupp.ctx").create().cursor@m(PS_PARTKEY,   PS_SUPPKEY,PS_SUPPLYCOST;A5.find(PS_PARTKEY);A1)
8	=A7.fetch().derive@o().keys@i(PS_PARTKEY,PS_SUPPKEY)
9	=file(path+"orders.ctx").create().cursor@m(O_ORDERKEY,O_ORDERDATE;;A1)
10	=file(path+"lineitem.ctx").create().news(A9,L_ORDERKEY,L_PARTKEY,L_SUPPKEY,L_QUANTITY,L_EXTENDEDPRICE,L_DISCOUNT,O_ORDERDATE;A8.find(L_PARTKEY,L_SUPPKEY))
11	=A10.join@i(L_PARTKEY:L_SUPPKEY,   A8,PS_SUPPLYCOST).switch(L_SUPPKEY,A6).new(L_ORDERKEY,O_ORDERDATE,L_SUPPKEY.S_NATIONKEY.N_NAME:nation,L_EXTENDEDPRICE*(1-L_DISCOUNT)-PS_SUPPLYCOST*L_QUANTITY:profit)
12	=A11.groups(nation,year(O_ORDERDATE):o_year;sum(profit))
13	=A12.sort(nation,o_year:-1)
14	=now()
15	=interval@s(A2,A14)

A10中用join@i方法把外键表的字段PS_SUPPLYCOST拼到lineitem游标上，这里是个双字段主键的外键表，不能再使用在游标建立时匹配并转换的方法。

 

脚本执行时间，单位：秒

并行数	1	2	4	8	12
Oracle	930	502	331	267	234
SPL组表	691	354	180	95	68