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

一、     查询要求

Q15语句查询获得某段时间内为总收入贡献最多的供货商(排名第一)的信息。可用以决定对哪些头等供货商给予奖励、给予更多订单、给予特别认证、给予鼓舞等激励。

Q15语句的特点是:带有分组、排序、聚集、聚集子查询操作并存的普通表与视图的连接操作。

 

二、     Oracle执行

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

create view revenue (supplier_no, total_revenue) as

         select

                   l_suppkey,

                   sum(l_extendedprice * (1 - l_discount))

         from

                   lineitem

         where

                   l_shipdate >= date '1995-04-01'

                   and l_shipdate < date '1995-04-01' + interval '3' month

         group by

                   l_suppkey;

 

select  /*+ parallel(n) */

         s_suppkey,

         s_name,

         s_address,

         s_phone,

         total_revenue

from

         supplier,

         revenue

where

         s_suppkey = supplier_no

         and total_revenue = (

                   select

                            max(total_revenue)

                   from

                            revenue

         )

order by

         s_suppkey;

 

drop view revenue;

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

脚本执行时间,单位:秒

并行数

1

2

4

8

12

Oracle

361

276

218

170

155

 

三、     SPL优化

这次查询分为两个阶段,先计算出视图revenue,然后再找出revenuetotal_revenue达到最大值的记录。前者是个常规的分组汇总,利用并行和列存提高性能。后者对于SQL来讲只能写成子查询,这就需要进行两次遍历。因为SQL没有引用和集合数据类型,不能直接返回最大值所在记录。而SPL提供了这样的语法,即可以返回最大值本身,也可以返回最大值所在记录,一次遍历就可以计算出来。

 

SPL脚本如下:


A

1

=1

2

=now()

3

>date=date("1995-04-01")

4

=elapse@m(date,3)

5

=file(path+"lineitem.ctx").create().cursor@m(L_SUPPKEY,L_EXTENDEDPRICE,L_DISCOUNT;L_SHIPDATE>=date   &&L_SHIPDATE< A4;A1)

6

=A5.groups@u(L_SUPPKEY:supplier_no;sum(L_EXTENDEDPRICE   * (1 - L_DISCOUNT)):total_revenue)

7

=A6.maxp@a(total_revenue)

8

=file(path+"supplier.ctx").create()

9

=A7.joinx@q(supplier_no,A8:S_SUPPKEY,S_NAME,S_ADDRESS,S_PHONE).fetch()

10

=now()

11

=interval@s(A2,A10)

A6计算出视图revenueA7maxp@a一次遍历即返回total_revenue最大的那些记录,完成这个运算后再去到supplier表中查出其它字段,减少计算量。

 

脚本执行时间,单位:秒

并行数

1

2

4

8

12

Oracle

361

276

218

170

155

SPL组表

99

52

29

20

18