从 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是并行数。

脚本执行时间，单位：秒

三、 SPL优化

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

SPL脚本如下：

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

脚本执行时间，单位：秒