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

一、     查询要求

Q11语句是查询库存中某个国家供应的零件的价值。

Q11语句的特点是:带有分组、排序、聚集、子查询操作并存的多表连接查询操作。子查询位于分组操作的HAVING条件中。

 

二、     Oracle执行

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

select  /*+ parallel(n) */

         ps_partkey,

         sum(ps_supplycost * ps_availqty) as value

from

         partsupp,

         supplier,

         nation

where

         ps_suppkey = s_suppkey

         and s_nationkey = n_nationkey

         and n_name = 'CHINA'

group by

         ps_partkey

having

         sum(ps_supplycost * ps_availqty) > (

                   select

                            sum(ps_supplycost * ps_availqty) * 0.000001

                   from

                            partsupp,

                            supplier,

                            nation

                   where

                            ps_suppkey = s_suppkey

                            and s_nationkey = n_nationkey

                            and n_name = 'CHINA'

         )

order by

         value desc;

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

脚本执行时间,单位:秒

并行数

1

2

4

8

12

Oracle

71

52

43

36

33

 

三、     SPL优化

我们把下面的子查询看成为视图V

                   select

                            ps_partkey,

                            sum(ps_supplycost * ps_availqty) as value

                   from

                            partsupp,

                            supplier,

                            nation

                   where

                            ps_suppkey = s_suppkey

                            and s_nationkey = n_nationkey

                            and n_name = 'CHINA'

则原主体查询等价于

                   select

                            ps_partkey,

                            value

                   from V

                   where value>0.000001*(select sum(value) from V)

         这个V已经是经过分组后的结果集,数量较小,遍历V比直接针对partsupp遍历的计算量要小很多。

         而我们还知道,partsupp表是按主键ps_partkey,ps_suppkey有序的,也就是对ps_partkey有序,这时候针对该字段的分组可以使用有序分组方案,以提高计算V的性能。

 

SPL脚本如下:


A

1

=1

2

=now()

3

>name="CHINA"

4

>percent=0.000001

5

=file(path+"nation.ctx").create().cursor(N_NATIONKEY,N_NAME;N_NAME   == name).fetch().keys@i(N_NATIONKEY)

6

=file(path+"supplier.ctx").create().cursor@m(S_SUPPKEY;A5.find(S_NATIONKEY);A1)

7

=A6.fetch().keys@i(S_SUPPKEY)

8

=file(path+"partsupp.ctx").create().cursor@m(PS_PARTKEY,PS_AVAILQTY,PS_SUPPLYCOST;A7.find(PS_SUPPKEY);A1)

9

=A8.fetch()

10

=A9.groups@o(PS_PARTKEY;sum(PS_SUPPLYCOST   * PS_AVAILQTY):value)

11

=A10.sum(value)   * percent

12

=A10.select(value   > A11)

13

=A12.sort@o(value:-1)

14

=now()

15

=interval@s(A2,A14)

A10groups@o执行有序分组,相当于计算出视图V,然后A11,A12A10进行两次遍历计算出结果。

 

脚本执行时间,单位:秒

并行数

1

2

4

8

12

Oracle

71

52

43

36

33

SPL组表

24

15

9

6

5