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

一、 查询要求

Q7语句是查询从供货商国家与销售商品的国家之间通过销售获利情况的查询。此查询确定在两国之间货运商品的量用以帮助重新谈判货运合同。

Q7语句的特点是：带有分组、排序、聚集、子查询操作并存的多表查询操作。子查询的父层查询不存在其他查询对象，是格式相对简单的子查询。

二、 Oracle执行

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

select  /+ parallel(n) /

         supp_nation,

         cust_nation,

         l_year,

         sum(volume) as revenue

from

         (

                   select

                            n1.n_name as supp_nation,

                            n2.n_name as cust_nation,

                            extract(year from l_shipdate) as l_year,

                            l_extendedprice * (1 - l_discount) as volume

                  from

                            supplier,

                            lineitem,

                            orders,

                            customer,

                            nation n1,

                            nation n2

                   where

                            s_suppkey = l_suppkey

                            and o_orderkey = l_orderkey

                            and c_custkey = o_custkey

                            and s_nationkey = n1.n_nationkey

                            and c_nationkey = n2.n_nationkey

                            and (

                                     (n1.n_name = 'CHINA' and n2.n_name = 'RUSSIA')

                                     or (n1.n_name = 'RUSSIA' and n2.n_name = 'CHINA')

                            )

                            and l_shipdate between date '1995-01-01' and date '1996-12-31'

         ) shipping

group by

         supp_nation,

         cust_nation,

         l_year

order by

         supp_nation,

         cust_nation,

         l_year;

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

脚本执行时间，单位：秒

三、 SPL优化

中间子查询的运算和Q3类似，优化原理也类似，这里就不再赘述。

SPL脚本如下：

注意nation表数据A5在A6和A7分别使用了一次，用于外键匹配过滤，这和SQL的别名写法不同。

脚本执行时间，单位：秒