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

脚本执行时间，单位：秒

三、 SPL优化

这里orders与lineitem主子表关联的优化原理同Q3。

SPL脚本如下：

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

脚本执行时间，单位：秒