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

一、 查询要求

Q21语句查询获得不能及时交货的供货商。

Q21语句的特点是：带有分组、排序、聚集、EXISTS子查询、NOT EXISTS子查询操作并存的四表连接操作。查询语句没有从语法上限制返回多少条元组，但是TPC-H标准规定，查询结果只返回前100行（通常依赖于应用程序实现）。

二、 Oracle执行

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

select * from (

         select  /+ parallel(n) /

                   s_name,

                   count(*) as numwait

         from

                   supplier,lineitem l1,orders,nation

         where

                   s_suppkey = l1.l_suppkey

                   and o_orderkey = l1.l_orderkey

                   and o_orderstatus = 'F'

                   and l1.l_receiptdate > l1.l_commitdate

                   and exists (

                            select

                                     *

                            from

                                     lineitem l2

                            where

                                     l2.l_orderkey = l1.l_orderkey

                                     and l2.l_suppkey <> l1.l_suppkey

                   )

                   and not exists (

                            select

                                     *

                            from

                                     lineitem l3

                            where

                                     l3.l_orderkey = l1.l_orderkey

                                     and l3.l_suppkey <> l1.l_suppkey

                                     and l3.l_receiptdate > l3.l_commitdate

                   )

                   and s_nationkey = n_nationkey

                   and n_name = 'CHINA'

         group by

                   s_name

         order by

                   numwait desc,

                   s_name

) where rownum<=100;

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

脚本执行时间，单位：秒

三、 SPL优化

本问题的主查询比较简单，就是前面介绍过的主子表和外键表的连接。这里麻烦的主要是两个有exists的子查询。仔细研究这两个子查询可以发现，它们都是针对同一个l_orderkey下的lineitem记录的一些运算。

我们知道，lineitem已经按l_orderkey排序，而且子表记录可以看作是主表的集合字段，如果我们将orders和lineitem的连接结果集按orderkey做有序分组（但先不聚合），可以得到一个个由相同l_orderkey值的lineitem记录的小集合，再在这个小集合中去计算上述那两个exists条件就会比较简单一些。

SQL没有显式的集合数据类型，没办法描述这种运算过程。而SPL提供有这种数据类型，就可以实现这个思路了。

SPL脚本如下：

A5中对orders和lineitem做连接，A6再对其做有序分组，分成由相同l_orderkey值的lineitem记录集合，A7中对这些小集合实现exists判断，过滤掉不满足条件的。后面的代码把其它外键表关联上再做常规分组运算即可。

脚本执行时间，单位：秒