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

一、     查询要求

 

Q22语句查询获得消费者可能购买的地理分布。本查询计算在指定的国家，比平均水平更持肯定态度但还没下七年订单的消费者数量。能反应出普通消费者的态度，即购买意向。

Q22语句的特点是：带有分组、排序、聚集、子查询、NOT EXISTS子查询操作并存的两表连接操作。

 

 

二、     Oracle执行

 

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

select  /*+ parallel(n) */

         cntrycode,

         count(*) as numcust,

         sum(c_acctbal) as totacctbal

from

         (

                   select

                            substr(c_phone,1,2) as cntrycode,

                            c_acctbal

                   from

                            customer

                   where

                            substr(c_phone,1,2) in

                                     ('11', '14', '15', '19', '20', '21', '23')

                            and c_acctbal > (

                                     select

                                               avg(c_acctbal)

                                     from

                                               customer

                                     where

                                               c_acctbal > 0.00

                                               and substr(c_phone,1,2) in

                                                        ('11', '14', '15', '19', '20', '21', '23')

                            )

                            and not exists (

                                     select

                                               *

                                     from

                                               orders

                                     where

                                               o_custkey = c_custkey

                            )

         ) custsale

group by

         cntrycode

order by

         cntrycode;

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

脚本执行时间，单位：秒

并行数	1	2	4	8	12
Oracle	128	86	70	53	48

 

 

三、     SPL优化

 

这个查询的外层是个常规的分组聚合，其主要复杂度在于内层的两个用于条件的子查询。这两个子查询能对应一些满足条件的customer记录，而后一个子查询是用了not exist修饰，最后结果应当是这两个子查询对应的customer记录集合的差集。

 

SPL脚本如下：

	A
1	=1
2	=now()
3	=["11",   "14", "15", "19", "20",   "21", "23"]
4	=file(path+"customer.ctx").create().cursor@m(C_CUSTKEY,C_PHONE,C_ACCTBAL;C_ACCTBAL   > 0.0 && A3.contain(left(C_PHONE,2));A1).fetch()
5	=A4.avg(C_ACCTBAL)
6	=A4.select(C_ACCTBAL   > A5).derive@o().keys@i(C_CUSTKEY)
7	=file(path+"orders.ctx").create().cursor@m(O_CUSTKEY;A6.find(O_CUSTKEY);A1)
8	=A7.groups(O_CUSTKEY:C_CUSTKEY)
9	=[A6,A8].merge@d(C_CUSTKEY)
10	=A9.groups(left(C_PHONE,2):cntrycode;   count(1):numcust, sum(C_ACCTBAL):totacctbal)
11	=now()
12	=interval@s(A2,A11)

SPL中分组计算结果是有序的，即A8会对C_CUSTKEY有序，而A6本身来自customer表，也对C_CUSTKEY有序，两个有序集可以使用归并算法高速计算差集（A9中）。

 

脚本执行时间，单位：秒

并行数	1	2	4	8	12
Oracle	128	86	70	53	48
SPL组表	102	53	29	24	19