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

一、     查询要求

 

Q16语句查询获得能够以指定的贡献条件供应零件的供货商数量。可用于决定在订单量大，任务紧急时，是否有充足的供货商。

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

 

 

二、     Oracle执行

 

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

select  /*+ parallel(n) */

         p_brand,p_type,p_size,

         count(distinct ps_suppkey) as supplier_cnt

from

         partsupp,part

where

         p_partkey = ps_partkey

         and p_brand <> 'Brand#21'

         and p_type not like 'SMALL%'

         and p_size in (2, 15, 17, 23, 25, 41, 44, 45)

         and ps_suppkey not in (

                   select

                            s_suppkey

                   from

                            supplier

                   where

                            s_comment like '%Customer%Complaints%'

         )

group by

         p_brand,

         p_type,

         p_size

order by

         supplier_cnt desc,

         p_brand,

         p_type,

         p_size;

 

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

脚本执行时间，单位：秒

并行数	1	2	4	8	12
Oracle	57	34	23	15	13

 

 

三、     SPL优化

 

这个查询是将partsupp用两个外键表part和supplier进行匹配过滤后再分组，分组时将用两个外键表字段。这时候采用前面所述的技巧，先将外键表按条件过滤后，再将主表的关联字段与外键表匹配并把匹配上的关联字段转换成外键表的记录指针，这样匹配不上的记录 可直接过滤掉，而且在后面分组运算时即可直接引用外键表字段。

 

SPL脚本如下：

	A
1	=1
2	=now()
3	>brand="Brand#21"
4	>type="SMALL*"
5	>sizes=[2,   15, 17, 23, 25, 41, 44, 45]
6	>sizes.sort@o()
7	=file(path+"part.ctx").create().cursor@m(P_PARTKEY,P_BRAND,P_TYPE,P_SIZE;P_BRAND   != brand && !like(P_TYPE,type) &&   sizes.pos@b(P_SIZE)!=null;A1).fetch().derive@o().keys@i(P_PARTKEY)
8	=file(path+"supplier.ctx").create().cursor@m(S_SUPPKEY;!like(S_COMMENT,"*Customer*Complaints*");A1).fetch().keys@i(S_SUPPKEY)
9	=file(path+"partsupp.ctx").create().cursor@m(PS_PARTKEY,PS_SUPPKEY;PS_PARTKEY:A7,PS_SUPPKEY:A8;A1)
10	=A9.groups@u(PS_PARTKEY.P_BRAND:P_BRAND,PS_PARTKEY.P_TYPE:P_TYPE,PS_PARTKEY.P_SIZE:P_SIZE;icount(PS_SUPPKEY):supplier_cnt)
11	=A10.sort@o(supplier_cnt   :-1,P_BRAND,P_TYPE,P_SIZE)
12	=now()
13	=interval@s(A2,A12)

A7和A8分别读来并过滤外键表，A9建立游标同时做匹配和过滤。

注意A7中做in判断时使用了pos@b，表示这里将采用二分法。当in判断的集合成员较多时，将成员先排序后使用二分法可以减少比较次数，从而提高性能。

 

脚本执行时间，单位：秒

并行数	1	2	4	8	12
Oracle	57	34	23	15	13
SPL组表	39	26	18	11	10

本问题涉及数据量不大，SPL的运算结果与SQL差别也不大。