一、     查询要求

Q17 语句对于指定品牌和指定包装类型的零件，查询出订购量少于此类零件平均订购量 20％的订单平均每年的订购额。所以此查询可用于计算出如果没有小量订单，平均年收入将损失多少。

Q17语句的特点是：带有聚集、聚集子查询操作并存的两表连接操作。

二、     Oracle执行

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

select  /*+ parallel(n) */

         sum(l_extendedprice) / 7.0 as avg_yearly

from

         lineitem,part

where

         p_partkey = l_partkey

         and p_brand = 'Brand#33'

         and p_container = 'LG DRUM'

         and l_quantity < (

                   select

                            0.2 * avg(l_quantity)

                   from

                            lineitem

                   where

            l_partkey = p_partkey

         );

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

脚本执行时间，单位：秒

三、     SPL优化

这种在子查询中用等值条件与主表关联的情况，都可以转换成JOIN来计算，从而可以利用JOIN的优化技术。

select  /*+ parallel(n) */

         sum(l_extendedprice) / 7.0 as avg_yearly

from

         lineitem,part,

         (select l_partkey lp, 0.2*avg(l_quantity) lq

                   from lineitem

                   group by l_partkey

         ) lpq

where

         p_partkey = l_partkey

         and p_brand = 'Brand#33'

         and p_container = 'LG DRUM'

         and l_partkey=lp

and l_quantity < lq

这相当于对lineitem表进行两次外键匹配过滤，其中一个外键表是子查询计算出来的中间表lpq，而因为是内连接，lpq中涉及到的partkey也只在part表过滤后的范围内，因此可以复用part表的过滤。

SPL脚本如下：

脚本执行时间，单位：秒